Split a table into multiple smaller tables based on a column's value-Power Query

0
votes

I have a table like this that I got using "combine & edit" option in power query that has information from multiple sheets from multiple .xlsx files. Sheetnames never change and they'll stay the same, excel files can change.

enter image description here

Now, I want many tables splitting by column1's value firstkey. So, I can get multiple tables like this,

enter image description here

I have been Googling to find an answer, still no success. There are threads like this, that requires you to duplicate the original table and filter each value.

However, in my case, I want to automate in a way if I have new .xlsx files. So, if I get a value Brooklyn Park instead of Bursville, it should be filtered based on Column1's value.

How can I do this Power Query?

EDIT

As requested, original excel sheet for one file,

enter image description here

M code:

let
    Source = Excel_Export,
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each ([Source.Name] = "Burnsville.xlsx")),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows"),
    #"Removed Top Rows" = Table.Skip(#"Transposed Table",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Address", "Address Number"}, {"Column3", "StreetName"}, {"Column4", "City"}})
in
    #"Renamed Columns"

I used this code to create a function to automate for each file.

2
excelpowerquerym
It might be worth creating a custom M function which extracts the data from each sheet in a workbook (in the manner/format that you want) -- and then applying that function to the workbooks you want -- and then combining the result into a table. Kind of like what was done here: stackoverflow.com/q/53365065/8811778. Otherwise, based on the screenshot, it seems like you just want to insert an empty row before each instance of firstKey in Column1 -- is that correct? – chillin
@chillin I did a function like this. It was helpful. Also, if i can have an empty row between each table, that would awesome. However,I want to do for a whole folder of files and not for each file in a folder. Then later I can append them together with may be another function. – user9431057
#1 Is your first table (in the first image) already dynamic? E.g. will it pick up new .xlsx files when you refresh it? #2 I'm not sure what you mean by multiple tables. It should be possible to insert a blank row before each instance of firstKey in Column1, but it would still load as a single table in Excel. #3 I don't understand what you mean by "if I get a value Brooklyn Park instead of Bursville, it should be filtered based on Column1's value.". How does a new value affect the table? Would it appear in the left column of the table? – chillin
#4 I don't see what the difference is between "I want to do for a whole folder of files and not for each file in a folder". My thinking was to use Folder.Files function to get a list of .xlsx files in a particular folder. Then apply the function to each file. The output would be a column/list of M tables, which can then be appended/combined into a single table -- and then that single table gets loaded to your Excel sheet. – chillin
@chillin #1. yes, it'll be dynamic. #3. Sorry, I should have been clear, "Burnsville", "BrooklynPark" (cities in Minnesota) are file names. They are there because, I combined all the files together, that's why I have them and I would still keep them. #4. Since these files are dynamic, I would like to point to a folder to read files. – user9431057

2 Answers

1
votes

The M code you've posted indicates there being at least 3 columns, but your first image shows only two columns. It also appears to refer to another query (Excel_Export). I was expecting it to show how you achieved the table in the first image, so am not too sure what's going on.

Regarding the insertion of blank rows, you can try the function below.

Code:

fxInsertBlankRows = (tableToTransform as table) =>
    let
        blankRowToInsert = 
            let
                headers = Table.ColumnNames(tableToTransform),
                emptyTable = Table.FromColumns(List.Transform(headers, each {""}), headers),
                toListOfRecords = Table.ToRecords(emptyTable)
            in
                toListOfRecords,
        insertionIndexes =
            let
                isolateColumn = Table.SelectColumns(tableToTransform, {"Column1"}),
                indexes = Table.PositionOf(isolateColumn, [Column1="firstKey"], Occurrence.All)
            in
                indexes,
        insertBlankRows = List.Accumulate(insertionIndexes, tableToTransform, (tableState, currentIndex) =>
                Table.InsertRows(tableState, currentIndex, blankRowToInsert)
            ) 
    in
        insertBlankRows,

Say you want to use the above function on the #"Renamed Columns" step in the M code you posted (assuming #"Renamed Columns" is a table, which I'm fairly sure it is). You would change the way the code ends as per below:

#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Address", "Address Number"}, {"Column3", "StreetName"}, {"Column4", "City"}})
fxInsertBlankRows = (tableToTransform as table) =>
        let
            blankRowToInsert = 
                let
                    headers = Table.ColumnNames(tableToTransform),
                    emptyTable = Table.FromColumns(List.Transform(headers, each {""}), headers),
                    toListOfRecords = Table.ToRecords(emptyTable)
                in
                    toListOfRecords,
            insertionIndexes =
                let
                    isolateColumn = Table.SelectColumns(tableToTransform, {"Column1"}),
                    indexes = Table.PositionOf(isolateColumn, [Column1="firstKey"], Occurrence.All)
                in
                    indexes,
            insertBlankRows = List.Accumulate(insertionIndexes, tableToTransform, (tableState, currentIndex) =>
                    Table.InsertRows(tableState, currentIndex, blankRowToInsert)
                ) 
        in
            insertBlankRows,
invokeFunction = fxInsertBlankRows(#"Renamed Columns")
in
    invokeFunction
0
votes

Seemed like a fun challenge. Here's a standalone example, I tried to make it succinct:

let
    SourceTable = Table.FromRecords({
        [Cities = "City1", Info = "Info1"],[Cities = "City1", Info = "Info2"],
        [Cities = "City1", Info = "Info3"],[Cities = "City2", Info = "Info1"],
        [Cities = "City2", Info = "Info2"],[Cities = "City3", Info = "Info1"],
        [Cities = "City3", Info = "Info2"],[Cities = "City3", Info = "Info3"],
        [Cities = "City3", Info = "Info4"],[Cities = "City3", Info = "Info5"]
    }),

    SortedTable = Table.Sort(SourceTable,{{"Cities", Order.Ascending},{"Info", Order.Ascending}}),

    DistinctCities = List.Distinct(SortedTable[Cities]),

    DistinctCitiesAfterFirst = if List.Count(DistinctCities) > 1 then List.RemoveRange(DistinctCities,0) else {},

    CityOffsets = List.Transform(DistinctCitiesAfterFirst, each (List.PositionOf(SortedTable[Cities],_) + List.PositionOf(DistinctCitiesAfterFirst,_) - 1)),

    SortedTableWithBlankRows = List.Accumulate(
        CityOffsets,
        SortedTable,
        ((tableState, currentOffset) =>
            Table.InsertRows(
                tableState,
                currentOffset,
                {
                    Record.FromList(List.Repeat({""},Table.ColumnCount(SortedTable)),Table.ColumnNames(SortedTable))
                }
            )
        )
    )

in
    SortedTableWithBlankRows