Forum Discussion
Need help with Pivot / Unpivot
Hi,
My objective to have these columns. I have tried pivot unpivot but couldn't quite get there...I have attached the pbix.
Inventory Type | Dealer Part | Max | Multiplier | Max_1 | Multiplier _1 | Max_2 | Multipler_2 |..."
PBIX:
https://drive.google.com/file/d/12KJ2gILZUuJNssS_xtJpUsOlTFFojE2o/view?usp=sharing
CSV:
https://drive.google.com/file/d/1_NiTSHrpBSTFl2_NmJ3Qcg-kqE8VrOOW/view?usp=sharing
TIA!
Hi,
This M code works
let Source = Csv.Document(File.Contents("C:\Users\mathu\Desktop\combined.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Inventory Type", "Dealer Part"}, "Attribute", "Value"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index1", 1, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Inventory Type", "Dealer Part", "Attribute"}, {{"Count", each Table.AddIndexColumn(_,"Counter",1,1), type table [Inventory Type=nullable text, Dealer Part=nullable text, Attribute=text, Value=text, Index1=number, Counter=number]}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Value", "Index1", "Counter"}, {"Value", "Index1", "Counter"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Count", {{"Counter", type text}}, "en-IN"),{"Attribute", "Counter"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Sorted Rows" = Table.Sort(#"Merged Columns",{{"Index1", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index1"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value") in #"Pivoted Column"Hope this helps.
5 Replies
- Ashish_Mathur
Super User
Hi,
This M code works
let Source = Csv.Document(File.Contents("C:\Users\mathu\Desktop\combined.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Inventory Type", "Dealer Part"}, "Attribute", "Value"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index1", 1, 1, Int64.Type), #"Grouped Rows" = Table.Group(#"Added Index", {"Inventory Type", "Dealer Part", "Attribute"}, {{"Count", each Table.AddIndexColumn(_,"Counter",1,1), type table [Inventory Type=nullable text, Dealer Part=nullable text, Attribute=text, Value=text, Index1=number, Counter=number]}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Value", "Index1", "Counter"}, {"Value", "Index1", "Counter"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Count", {{"Counter", type text}}, "en-IN"),{"Attribute", "Counter"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Sorted Rows" = Table.Sort(#"Merged Columns",{{"Index1", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index1"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value") in #"Pivoted Column"Hope this helps.
- Ashish_Mathur
You are welcome. The sole purpose of that column is to use it to sort the table in ascending order (before Pivoting back).
- amitchandak
Anonymous , First unpivot other by seleting Inventory Type | Dealer Part
Then Split by Delimiter using _
and then pivot again
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
Pivot Data(Power Query) :https://www.youtube.com/watch?v=oKByyI09Bno&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=12Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag
Need source data as excel or table pasted here, PBI does not provide source
amitchandak Hi Amit, here is the csv file.
https://drive.google.com/file/d/1_NiTSHrpBSTFl2_NmJ3Qcg-kqE8VrOOW/view?usp=sharing
