Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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 |..."

SK_JOB_1-1698198295154.png

 

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!

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
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_0-1698205957933.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
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_0-1698205957933.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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!

You are welcome.  The sole purpose of that column is to use it to sort the table in ascending order (before Pivoting back).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable
amitchandak
Super User
Super User

@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=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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!