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

Question

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.

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

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,

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.

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: https://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, Nov 24 '18 at 12:40
@chillin I did a function like [this](https://www.youtube.com/watch?v=Tnw77rpm-BQ). 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, Nov 24 '18 at 18:43
**#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, Nov 24 '18 at 19:52
**#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, Nov 24 '18 at 19:58
@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, Nov 25 '18 at 00:18
@chillin Hope this helps and thanks for the questions to try to help me. — user9431057, Nov 25 '18 at 00:18
could you please: include a screenshot of a sheet from any single workbook (maybe `Burnsville.xlsx` will do) and also include the M code for the table in the first image (one way of doing this might be: `Data > Show queries`, then `right-click the query > Edit > Advanced Editor`, then copy-paste the code to include it in your original post). I will try to give it a go, but I need to know the original structure of the data. — chillin, Nov 25 '18 at 15:53
@chillin as requested, a screen shot and `M` are attached, let me know if this is helpful, otherwise, I will edit it. — user9431057, Nov 25 '18 at 17:14
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184233/discussion-between-chillin-and-user9431057). — chillin, Nov 25 '18 at 20:57

chillin · Answer 1 · 2018-11-26T19:30:42.793

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

ZenoArrow · Answer 2 · 2023-01-01T01:24:19.637

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

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

2 Answers2