Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to 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.
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.
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).
@amitchandak Hi Amit, here is the csv file.
https://drive.google.com/file/d/1_NiTSHrpBSTFl2_NmJ3Qcg-kqE8VrOOW/view?usp=sharing
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |