PowerBI - unpivot selected values from column

Question

I have a table which has a list of Id's and categories. See below:

id	Category	Value
1	Pref 1 Region	MTO
1	Pref 1 Area	MT99
1	Pref 1 Station	ST124
1	Pref 2 Region	MTO
1	Pref 2 Area	MT85
1	Pref 2 Station	ST420
1	Pref 3 Region	BSW
1	Pref 3 Area	BS88
1	Pref 3 Station	ST876
2	Pref 1 Region	GRT
2	Pref 1 Area	GT34
2	Pref 1 Station	STT555
2	Pref 2 Region	MTO
2	Pref 2 Area	MT99
2	Pref 2 Station	ST124
2	Pref 3 Region	BSW
2	Pref 3 Area	BS88
2	Pref 3 Station	ST876

I want to keep the Pref # Station and 'unpivot' the other categories Area and Region to separate columns, like below:

id	Category	Value	Region	Area
1	Pref 1 Station	ST124	MTO	MT99
1	Pref 2 Station	ST420	MTO	MT85
1	Pref 3 Station	ST876	BSW	BS88
2	Pref 1 Station	STT555	GRT	GT34
2	Pref 2 Station	ST124	MTO	MT99
2	Pref 3 Station	ST876	BSW	BS88

I've tried unpivoting columns in PowerQuery but the order isn't kept. Any help is much appreciated!

Which order you are referring to? And what is your code so far? — Anonymous
– Anonymous, Commented Jun 23, 2022 at 4:34

Anonymous · Accepted Answer · 2022-06-23 04:45:38Z

This will produce the 2nd table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Category", type text}, {"Value", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Category", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Category.1", "Category.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Category.2]), "Category.2", "Value"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {"id", "Category.1", "Region", "Area"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Category.1", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Category"),
    #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"id", "Category", "Value", "Region", "Area"})
in
    #"Reordered Columns"

Collectives™ on Stack Overflow

PowerBI - unpivot selected values from column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related