Today we are going to discuss about how to normalize tables in Power Query. Input data and expected result is provided here to get an idea about our requirement.
Method-1:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.ExpandListColumn(Table.TransformColumns(Source,{{"Subject", each
Text.Split(_,", ")}}),"Subject")
in
Result
Source step helps us to pick input data into power query editor.
In Result step,
Table.TransformColumns function will help us to make transformation in one column,
Arguments of this function,
In the table argument, input the name of the previous step 'Source'. Since the result of the 'Source' step is a table, it is appropriate to use it here.
In the 2nd argument, feed the transform operation in list format.
Begin the list with the name of the column we intend to transform.
Our approach involves splitting the text in each row of the 'Subject' column. To achieve this, we can utilize the Text.Split function, which will require the text as input and return a list.
The Table.ExpandListColumn function allows us to expand the list in the 'Subject' column while simultaneously repeating the student's name according to the number of items in the subject list.
Method-2:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.Combine( Table.TransformRows(Source, (f) =>
Table.FillDown(Table.FromColumns({{f[Student]}, Text.Split( f[Subject], ", ")},
Table.ColumnNames(Source) ),{"Student"})) )
in
Result
Rather than altering the column, we utilize the Table.TransformRows function to modify each row. Split the text in subject column of each row using Text. Split function. Form the data structure of list within list by wrapping student and subject data inside curly braces as shown below.
{f[Student]}
in each row student name contains one value; by wrapping it inside curly braces, it becomes a list.
{{f[Student]}, Text.Split( f[Subject], ", ")}
This list has 2 lists, we name it as list within list. Use the Table.FromColumns function to wrap these lists, creating a table from them. This function will turn each list into a column of the resulting table. 2nd argument of this function helps us to provide the column names.
We have now converted our 'Source' table into a list of tables with two columns, 'Student' and 'Subject'. In this method, we can populate the student names according to the number of subjects by utilizing the Table.FillDown function. Use Table.Combine function to combine the list of tables to get our desired result.
Commentaires