How to unpivot the wide dataset

[ngct1112]

Hi All,

is it possible I could unpivot the table to vertical format? Great Thanks

ID	Description	From	To	Price	From	To	Price	From	To	Price
1	AA	100	200	0.384	200	300	7.164	300	400	0.316
2	BB	100	200	0.647	200	300	1.262	300	400	0.585
3	CC	100	200	1.952	200	300	1.222	300	400	0.474
1	DD	100	200	0.282	200	300	0.476	300	400	0.641
2	EE	100	200	0.503	200	300	0.456	300	400	1.302
3	FF	100	200	0.705	200	300	0.243	300	400	4.778

ID	Description	From	To	Price
1	AA	100	200	0.384
1	AA	200	300	7.164
1	AA	300	400	0.316
1	DD	100	200	0.282
1	DD	200	300	0.476
1	DD	300	400	0.641
2	BB	100	200	0.647
2	BB	200	300	1.262
2	BB	300	400	0.585
2	EE	100	200	0.503
2	EE	200	300	0.456
2	EE	300	400	1.302
3	CC	100	200	1.952
3	CC	200	300	1.222
3	CC	300	400	0.474
3	FF	100	200	0.705
3	FF	200	300	0.243
3	FF	300	400	4.778

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Description", type text}, {"From", Int64.Type}, {"To", Int64.Type}, {"Price", type number}, {"From2", Int64.Type}, {"To3", Int64.Type}, {"Price4", type number}, {"From ", Int64.Type}, {"To5", Int64.Type}, {"Price6", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Description"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Attribute.1", Text.Trim, type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Trimmed Text", {{"ID", type text}}, "en-IN"),{"ID", "Attribute.1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index1", 1, 1),
    Partition = Table.Group(#"Added Index", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Description", "Value", "Index1", "Index"}, {"Description", "Value", "Index1", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index1", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "ID"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Merged.2]), "Merged.2", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"From", type number}, {"To", type number}, {"Price", type number}})
in
    #"Changed Type2"

Hope this helps.

[ngct1112]

@Ashish_Mathur , the solution looks brilliant and work for me.

However, is it possible you explain a little bit on this? it is a group function without using the group button?

= Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}})

[Ashish_Mathur]

You are welcome.  That step is basically restarting the index number column on every change in entry in the "Merged columns" column.

[amitchandak]

@ngct1112 , Refer if this can help : https://kohera.be/blog/power-bi/how-to-unpivot-twice/

Detailed steps has been provided by Author.

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.

[Greg_Deckler]

@ngct1112 I did it like the attached PBIX file.