Power Query - Table Group with variable number of columns

0
votes

I need to use a parameterized key in Table. Group function in power query as the number of columns I need to group by, varies for each case. Example: I have 5 columns that could be used for Group by in the table. For one case I might need 1 column to group by, for another 3. In excel I have set up a table for this with 5 rows, which will have required column headers(1 or 2 or 3 or 4 or 5) I need for the case in question. How can I parameterize the key reference with the excel table(which has required number of column headers), so that updating it will update the group by clause successfully.

2
group-bypowerbiparameter-passingpowerquerym

2 Answers

0
votes

This function groups the specified table, by the specified number of columns:

(MyTable as table, MyColumns as number) =>
let
    #"Grouped Rows" = Table.Group(MyTable, List.FirstN(Table.ColumnNames(MyTable),MyColumns), {})
in
    #"Grouped Rows"
0
votes

You need to construct a record column with the desired number of columns in each row, and then group by that column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChType = Table.TransformColumnTypes(Source,{{"GrpBy", type text}}),
    CreateGroupBy = Table.AddColumn(ChType, "RecordCol", (row) => Record.SelectFields(row, Text.Split(row[GrpBy], ",")) ),
    Grouped = Table.Group(CreateGroupBy, {"RecordCol"}, {{"Summed", each List.Sum([Value]), type number}, {"Data", each _, type table}}),
    Expand = Table.ExpandRecordColumn(Grouped, "RecordCol", {"Col2", "Col3", "Col4"}, {"Col2", "Col3", "Col4"})
in
    Expand

You will need to replace record building part with your conditionals and maybe explicit record construction like if [Col1] = "1" then [f1 = [Col1], f2 = ""] else [f1 = "", f2 = [Col1]&[Col2]]

Having this source

GrpBy          | Col2 | Col3 | Col4 | Value
---------------+------+------+------+------
Col2           | A    | Q    | 1    | 10
Col2,Col3      | A    | W    | 2    | 20
Col2,Col4      | B    | W    | 4    | 30
Col2,Col3,Col4 | C    | E    | 4    | 40
Col3,Col4      | D    | E    | 4    | 50
Col2,Col3      | E    | R    | 4    | 60
Col2,Col3      | A    | W    | 2    | 70
Col2,Col4      | B    | X    | 4    | 80

you'll get this result

Col2 | Col3 | Col4 | Summed
-----+------+------+--------
A    |      |      | 10
A    | W    |      | 90
B    |      | 4    | 110
C    | E    | 4    | 40
     | E    | 4    | 50
E    | R    |      | 60

Like this