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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chrisjr
Helper IV
Helper IV

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 IV
Helper IV

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors