Forum Discussion
Table transformation correct format
Hello,
I am quite new to the Power Bi and in need of help on how to properly prepare the data before loading
trying to transforming a dataset having ( 35 columns ) having value of each column by mintue using power query but cannot seem to find the correct way,
The Data is reading from different meters and some categories are having 3 attributes,
The dataset is having a column for date, row for electricity and under it should be 3 rows for each meter sensor, the same applies to production and data flow see the image
also another example would be Machie 1 having 3 attributes ( Electric consumption, Temp, Returning Temp)
What will be the best way to transform the data to have a column for the 1st row? so it should be as the following:
Date Attribute Meter 1 Meter 2 Meter 3 West 1 West 2
1-1-21 Electrictiy 56 55 77 null null
1-1-21 Production null null null 55 22
After doing transpose and filling down the data and transpose again i got the below, but still not the structure that i want so i can get the value from each machine or category but it self if needed.
Also will this way i will be able to load and do the visualization correctly ?
Hi Anonymous ,
You can try this query:
let Source = Excel.Workbook(File.Contents("xxx\xx.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Filled Up" = Table.FillUp(Sheet1_Sheet,{"Column1"}), #"Removed Other Columns" = Table.Distinct(Table.SelectColumns(#"Filled Up",{"Column1"})), #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1"}), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column1", "Attribute"}}), Custom1 = Table.AddColumn(#"Removed Other Columns","New",each #"Renamed Columns"), #"Expanded New" = Table.ExpandTableColumn(Custom1, "New", {"Attribute", "Column2", "Column3"}, {"New.Attribute", "New.Column2", "New.Column3"}), #"Pivoted Column" = Table.Pivot(#"Expanded New", List.Distinct(#"Expanded New"[New.Column2]), "New.Column2", "New.Column3", List.Sum), #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"New.Attribute", "Attribute"}, {"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}, {"Attribute", type text}, {"Meter 1", Int64.Type}, {"Meter 2", Int64.Type}, {"Meter 3", Int64.Type}, {"West 1", Int64.Type}, {"West 2", type number}, {"East 1", Int64.Type}, {"East 2", type number}}) in #"Changed Type"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hello Anonymous ,
you can unpivot the top 2 rows at a time using my function over here: Unpivot by number of columns and rows in PowerBI and PowerQuery in Excel – The BIccountant
Then "pivot back" with the column containing the meter names.Hi Anonymous ,
You can try this query:
let Source = Excel.Workbook(File.Contents("xxx\xx.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Filled Up" = Table.FillUp(Sheet1_Sheet,{"Column1"}), #"Removed Other Columns" = Table.Distinct(Table.SelectColumns(#"Filled Up",{"Column1"})), #"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1"}), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column1", "Attribute"}}), Custom1 = Table.AddColumn(#"Removed Other Columns","New",each #"Renamed Columns"), #"Expanded New" = Table.ExpandTableColumn(Custom1, "New", {"Attribute", "Column2", "Column3"}, {"New.Attribute", "New.Column2", "New.Column3"}), #"Pivoted Column" = Table.Pivot(#"Expanded New", List.Distinct(#"Expanded New"[New.Column2]), "New.Column2", "New.Column3", List.Sum), #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"New.Attribute", "Attribute"}, {"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}, {"Attribute", type text}, {"Meter 1", Int64.Type}, {"Meter 2", Int64.Type}, {"Meter 3", Int64.Type}, {"West 1", Int64.Type}, {"West 2", type number}, {"East 1", Int64.Type}, {"East 2", type number}}) in #"Changed Type"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
