March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 ?
Solved! Go to Solution.
Hi @Osama14 ,
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.
Hi @Osama14 ,
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.
I would first make a new query to get the current column names into a list, then just Promote headers, then select your new Column1, and then click "Unpivot other columns". Then you can rename the columns at once, using your list of columns as the second parameter.
That's it!
--Nate
Hello @Osama14 ,
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.