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.
I know the tricks of unpivotting to flatten out an excel matrix to a table. But I have multiple measures (revenue, and quantity) that I want to separate into different measure columns (see below).
How to do this? Thanks so much.
NM
Solved! Go to Solution.
Hi @nanma94,
Here is the code that you can use. I have assumed that the years stay the same for Revenue and Qty
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}}), #"Removed Top Rows" = Table.Skip(#"Changed Type",1), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Continent"}, {"Column2", "Country"}, {"Year", "City/State"}}), #"Filled Down" = Table.FillDown(#"Renamed Columns",{"Continent", "Country"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"City/State", "Country", "Continent"}, "Year", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Type", each if Text.Contains([Year], "_") then "Qty" else "Rev" ), #"Extracted First Characters" = Table.TransformColumns(#"Added Conditional Column", {{"Year", each Text.Start(_, 4), type text}}), #"Pivoted Column" = Table.Pivot(#"Extracted First Characters", List.Distinct(#"Extracted First Characters"[Type]), "Type", "Value") in #"Pivoted Column"
Here is the snapshot of the result
Download the excel file from here
Thanks
Hi @nanma94,
Here is the code that you can use. I have assumed that the years stay the same for Revenue and Qty
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}}), #"Removed Top Rows" = Table.Skip(#"Changed Type",1), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Continent"}, {"Column2", "Country"}, {"Year", "City/State"}}), #"Filled Down" = Table.FillDown(#"Renamed Columns",{"Continent", "Country"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"City/State", "Country", "Continent"}, "Year", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Type", each if Text.Contains([Year], "_") then "Qty" else "Rev" ), #"Extracted First Characters" = Table.TransformColumns(#"Added Conditional Column", {{"Year", each Text.Start(_, 4), type text}}), #"Pivoted Column" = Table.Pivot(#"Extracted First Characters", List.Distinct(#"Extracted First Characters"[Type]), "Type", "Value") in #"Pivoted Column"
Here is the snapshot of the result
Download the excel file from here
Thanks
Hello Chandeep, i can't download the Excel file. Is it possible you check download permissions?
Thanks,
Alex-
Hi @Alex_Cepeda, The files don't need any permission to download
Excel file - When you open it in the browser, please click on File and choose Save As
Hope it helps
Hello ChandeepChhabra , i can't download the excel file. Could you please check download permissions?
Thanks!
Alex-
ChandeepChhabra, appreciate you looking into this!
Do you have the pbix you can share, so I can track the steps in GUI?
Thanks again.
NM
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.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |