Today, we will explore various methods in Power Query for de-normalizing a table using
M-Code. Input data and the expected result are provided to give an understanding of our requirements.
Download the exercise file with all three queries and try it yourself.
Method -1:
let
Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
Result = Table.Group(Source,{"Student"},{{"Subjects", each Text.Combine(_[Subject],", ")}})
in
Result
Grouping in Power Query is just that simple and straightforward.
Source step helps us to extract the table into Power Query editor.
In result step,
Table.Group function does group the table based on given key column.
This will keep the unique items of key column and will add an aggregated column.
1st argument is table, in which we fed 'Source' step.
2nd argument is key as any,
Enter the student column name as a list within curly braces.
3rd argument is aggregated columns as list,
Create a nested list data structure by using two opening curly braces,
and within the curly braces, provide 3 arguments separated by commas.
Argument 1 - provide new column name -> 'Subjects'.
Argument 2 - each is the custom function which will help us to do the calculation in each row of the table. _ is the place holder for result table.
Argument 3 [Optional argument] - is to define columns data type.
Close 2 curly braces.
= Table.Group(Source,{"Student"},{{"Subjects", each _}})
The result of the above construction is shown below.
As mentioned before, _ serves as a placeholder for the table. To select the subject column, we can utilize the Field access operator [] with the column name inside the brackets.
We now obtain a list of subjects for each student. Use the Text.Combine function with ", " as the separator to concatenate these lists of subjects.
Method - 2:
let
Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
Result = Table.FromRows(List.Transform(List.Distinct(Source[Student]), (f) => {f} &
{Text.Combine(Table.SelectRows(Source, (x) => x[Student] = f)[Subject],", ")}),
Table.ColumnNames(Source))
in
Result
This is completely different compared with method-1.
Let me start this from scratch.
=Source[Student])
This will extract student column as list.
=List.Distinct(Source[Student])
This will pick distinct items from student list.
List.Transform function will help us to transform the list based on our requirement.
in 2nd argument of transform as function,
(f) => is the variable used for each.
We will use 'f' as place holder for current value in list.
in first item transformation,
f -> "A",
Table.SelectRows(Source, (x) => x[Student] = f)
Here, (x) => is the variable used for each. This refers each record in Source table.
This will filter source table only for the records of student "A".
We can extract subject column as list using field access operators.
Combine the texts in list with Text. Combine function with ", " as separator.
Transform concatenated text into list by wrapping it inside curly braces.
Transform current value into list by wrapping it inside curly braces.
Append these lists to create list within list structure as shown below,
This transformation process will be carried out for each item in list.
The Table. FromRows function is used to create a table from nested lists.
Every item in a list is transformed into a row in the table.
Method - 3:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
Result = Table.FromRows(List.Accumulate(List.Distinct(Source[Student]),{}, (s,c) => s & {{c} & {Text.Combine(Table.SelectRows(Source, (x) => x[Student] = c)[Subject],", ")}}), Table.ColumnNames(Source))
in
Result
This is same as Method-2, but we accomplish the result using List. Accumulate function.
Comments