cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chrisjr
Helper II
Helper II

Unpivot multiple groups

Hello All, 

 

I  have the following source table .

 VolvoPeugeotMercedezAudi
 Volvo v40Peugeot 308A classA1
IndexA0,50,20,10,1
IndexB0,70,90,30,1
 Volvo v60Peugeot 307B classA2
IndexA0,20,30,10,5
IndexB0,30,40,10,2
 Vovo S40Peugeot 309C classA3
IndexA0,50,60,70,8
IndexB0,90,80,9

0,5

    

 

 

I am looking for a way to get all the car brands in one column; all the car model in another column and all the index with their values in another column. The end result would look like below:

Car BrandModelindexValue
Volvovolvo v60IndexA 
Volvovolvov40indexB 
Peugeotpeugeot 307INdexA 
Peugeotpeugeot 308indexB 

 

I have tried to first transpose and then unpivot all the columns exepct the first one but I can't get what I am looking for.

 

Anyone can help ?

thank you 



2 ACCEPTED SOLUTIONS
v-jingzhang
Community Support
Community Support

Hi @Chrisjr 

 

Here are my transformation steps. PBIX file has been attached at bottom. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCsvPKcsH0gGppemp+SVAlm9qUXJqSmoVkOlYmpKpFKsTraQAU6lQZmKAUK1gbGABUqaQnJNYXAxiGYKVe+alpFY4AvkGOqZg0ghMGkJJuBInsIA5mLQEk8ZISpAsNUO1FKTDCWGpEbqlRihmQZyBZilE0gRJiRGSpUA7g9E8CnKfM8JOY+weNUPykQW6nZZQYQQb6KxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Volvo", type text}, {"Peugeot", type text}, {"Mercedez", type text}, {"Audi", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Integer-Divided Column",{{"", "Brand"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Brand", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","","Model",Replacer.ReplaceValue,{"Brand"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Brand", "Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Model", each if [Brand] = "Model" then [Value] else null),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}, {"Attribute", Order.Ascending}, {"Brand", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Model"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Brand] <> "Model")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Attribute", "Model", "Brand", "Value"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Attribute", "Car Brand"}, {"Brand", "Index"}}),
    #"Sorted Rows1" = Table.Sort(#"Renamed Columns1",{{"Car Brand", Order.Ascending}, {"Model", Order.Ascending}, {"Index", Order.Ascending}})
in
    #"Sorted Rows1"

vjingzhang_0-1669776073772.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

wdx223_Daniel
Super User
Super User

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSUQrLzynLVygzMQCyA1JL01PzSxSMDSyAPEeF5JzE4mIQy1ApVidayTMvJbXCEcg30DEFk0Zg0hBKwpU4gQXMwaQlmDRGUoJkqRmqpSAdTghLjdAtNUIxC+IMNEshkiZISoyQLAXaGYzmUZD7nBF2GmP3qBmSjyzQ7bSECiPYQGfFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column1" = _t, Volvo = _t, Peugeot = _t, Mercedez = _t, Audi = _t]),
    Custom1 = let brands=List.Skip(Table.ColumnNames(Source)) in Table.Combine(Table.Group(Source,"Column1",{"n",each let a=Table.ToColumns(Table.DemoteHeaders(_)),b=Table.RowCount(_)-1 in Table.Combine(List.Transform(List.Skip(a),each Table.FromColumns(List.Zip(List.Repeat({List.FirstN(_,2)},2))&{List.Skip(a{0},2),List.Skip(_,2)},{"Car Brand","Model","Index","Value"})))},0,(x,y)=>Byte.From(Text.Remove(y," ")="" or y=null))[n])
in
    Custom1

wdx223_Daniel_0-1669784315518.png

 

View solution in original post

3 REPLIES 3
Chrisjr
Helper II
Helper II

@wdx223_Daniel Thank you for your reply. Apologies, I didnt mention my Power BI level is an entry level so I am not familiar yet with the query language.  

 

@v-jingzhang Thank you for you attached file, I applied the steps and it worked.

 

I now have 2 more questions:

1. at the end of the dataset, I have a last group for the average of all the index per brand (calculated with a weighting from other excel files so it's not just an average formula). The table looks like this:

 

 VolvoPeugeotMercedezAudi
 Volvo v40Peugeot 308A classA1
IndexA0,50,20,10,1
IndexB0,70,90,30,1
 Volvo v60Peugeot 307B classA2
IndexA0,20,30,10,5
IndexB0,30,40,10,2
 Vovo S40Peugeot 309C classA3
IndexA0,50,60,70,8
IndexB0,90,80,9

0,5

    

 

Final index0.60.30.5

0.8

Final index0.90.50.8

0.9

 

what would be the best way to transform it with the last group? or should I create another table just for these final index by filtering out all the other rows? 

 

 

2. This would be done in the process of an automatisation with excel files having the exact same structur but the number of columns would change depending on the number of brands. Would the steps mention by you @v-jingzhang still apply? 

 

 

 

Thank you for your help

 

wdx223_Daniel
Super User
Super User

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSUQrLzynLVygzMQCyA1JL01PzSxSMDSyAPEeF5JzE4mIQy1ApVidayTMvJbXCEcg30DEFk0Zg0hBKwpU4gQXMwaQlmDRGUoJkqRmqpSAdTghLjdAtNUIxC+IMNEshkiZISoyQLAXaGYzmUZD7nBF2GmP3qBmSjyzQ7bSECiPYQGfFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column1" = _t, Volvo = _t, Peugeot = _t, Mercedez = _t, Audi = _t]),
    Custom1 = let brands=List.Skip(Table.ColumnNames(Source)) in Table.Combine(Table.Group(Source,"Column1",{"n",each let a=Table.ToColumns(Table.DemoteHeaders(_)),b=Table.RowCount(_)-1 in Table.Combine(List.Transform(List.Skip(a),each Table.FromColumns(List.Zip(List.Repeat({List.FirstN(_,2)},2))&{List.Skip(a{0},2),List.Skip(_,2)},{"Car Brand","Model","Index","Value"})))},0,(x,y)=>Byte.From(Text.Remove(y," ")="" or y=null))[n])
in
    Custom1

wdx223_Daniel_0-1669784315518.png

 

v-jingzhang
Community Support
Community Support

Hi @Chrisjr 

 

Here are my transformation steps. PBIX file has been attached at bottom. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCsvPKcsH0gGppemp+SVAlm9qUXJqSmoVkOlYmpKpFKsTraQAU6lQZmKAUK1gbGABUqaQnJNYXAxiGYKVe+alpFY4AvkGOqZg0ghMGkJJuBInsIA5mLQEk8ZISpAsNUO1FKTDCWGpEbqlRihmQZyBZilE0gRJiRGSpUA7g9E8CnKfM8JOY+weNUPykQW6nZZQYQQb6KxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Volvo", type text}, {"Peugeot", type text}, {"Mercedez", type text}, {"Audi", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Integer-Divided Column",{{"", "Brand"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Brand", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","","Model",Replacer.ReplaceValue,{"Brand"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Brand", "Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Model", each if [Brand] = "Model" then [Value] else null),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}, {"Attribute", Order.Ascending}, {"Brand", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Model"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Brand] <> "Model")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Attribute", "Model", "Brand", "Value"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Attribute", "Car Brand"}, {"Brand", "Index"}}),
    #"Sorted Rows1" = Table.Sort(#"Renamed Columns1",{{"Car Brand", Order.Ascending}, {"Model", Order.Ascending}, {"Index", Order.Ascending}})
in
    #"Sorted Rows1"

vjingzhang_0-1669776073772.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors