Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I am working on a project in Power BI but I am having trouble getting the data loaded correctly.
the file looks like this,
and this is the goal state,
I have been trying unpiviot but I can't seem to get anything usable.
Any help appreciated!
Solved! Go to Solution.
@Anonymous
Go to New Source > Blank Query and click on View Tab and Advanced Editor, Paste the below code and modify the path to your Excel file. You can follow the steps to learn how I did it. No M coding needed,
let
Source = Excel.Workbook(File.Contents("C:\Users\Fowmy\OneDrive\BI\PBICommunity\Sample File.xlsx"), null, true),
#"Current State_Sheet" = Source{[Item="Current State",Kind="Sheet"]}[Data],
#"Removed Columns" = Table.RemoveColumns(#"Current State_Sheet",{"Column2"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"||ORDER"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"||ORDER", "ORDER"}, {"Attribute.1", "Production Line"}, {"Attribute.2", "Station"}, {"Attribute.3", "Resources"}, {"Value", "Time"}})
in
#"Renamed Columns"
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi, @Anonymous , it's a bit tricky to unpivot such a table as the headers consist of multiple hierarchies, pls refer to the steps below
let
Source = Excel.Workbook(File.Contents("D:\ChromeDownloads\Sample File.xlsx"), null, true),
#"Current State_Sheet" = Source{[Item="Current State",Kind="Sheet"]}[Data],
#"Transposed Table" = Table.Transpose(#"Current State_Sheet"),
#"Filled Up" = Table.FillUp(#"Transposed Table",{"Column1", "Column2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Filled Up", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"ORDER", "Resource"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Production Line", "Station", "Resource"}, "Order", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Order", "Production Line", "Station", "Resource", "Value"})
in
#"Reordered Columns"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous
Go to New Source > Blank Query and click on View Tab and Advanced Editor, Paste the below code and modify the path to your Excel file. You can follow the steps to learn how I did it. No M coding needed,
let
Source = Excel.Workbook(File.Contents("C:\Users\Fowmy\OneDrive\BI\PBICommunity\Sample File.xlsx"), null, true),
#"Current State_Sheet" = Source{[Item="Current State",Kind="Sheet"]}[Data],
#"Removed Columns" = Table.RemoveColumns(#"Current State_Sheet",{"Column2"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"||ORDER"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"||ORDER", "ORDER"}, {"Attribute.1", "Production Line"}, {"Attribute.2", "Station"}, {"Attribute.3", "Resources"}, {"Value", "Time"}})
in
#"Renamed Columns"
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.