Need help with Pivot / Unpivot

[SK_JOB]

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!

[Ashish_Mathur · Accepted solution]

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.

[SK_JOB]

This is just fantastic!!! Can you please explain me the reason to get index column? I know it works in the end run, but I couldn't quite grasp the reason to get index column and steps after that. Thank you so much!

[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).

[SK_JOB]

@amitchandak Hi Amit, here is the csv file.

https://drive.google.com/file/d/1_NiTSHrpBSTFl2_NmJ3Qcg-kqE8VrOOW/view?usp=sharing

[amitchandak]

@SK_JOB , 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=12

Power 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