Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have the following source table .
Volvo | Peugeot | Mercedez | Audi | |
Volvo v40 | Peugeot 308 | A class | A1 | |
IndexA | 0,5 | 0,2 | 0,1 | 0,1 |
IndexB | 0,7 | 0,9 | 0,3 | 0,1 |
Volvo v60 | Peugeot 307 | B class | A2 | |
IndexA | 0,2 | 0,3 | 0,1 | 0,5 |
IndexB | 0,3 | 0,4 | 0,1 | 0,2 |
Vovo S40 | Peugeot 309 | C class | A3 | |
IndexA | 0,5 | 0,6 | 0,7 | 0,8 |
IndexB | 0,9 | 0,8 | 0,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 Brand | Model | index | Value |
Volvo | volvo v60 | IndexA | |
Volvo | volvov40 | indexB | |
Peugeot | peugeot 307 | INdexA | |
Peugeot | peugeot 308 | indexB |
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
Solved! Go to Solution.
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"
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
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 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:
Volvo | Peugeot | Mercedez | Audi | |
Volvo v40 | Peugeot 308 | A class | A1 | |
IndexA | 0,5 | 0,2 | 0,1 | 0,1 |
IndexB | 0,7 | 0,9 | 0,3 | 0,1 |
Volvo v60 | Peugeot 307 | B class | A2 | |
IndexA | 0,2 | 0,3 | 0,1 | 0,5 |
IndexB | 0,3 | 0,4 | 0,1 | 0,2 |
Vovo S40 | Peugeot 309 | C class | A3 | |
IndexA | 0,5 | 0,6 | 0,7 | 0,8 |
IndexB | 0,9 | 0,8 | 0,9 | 0,5 |
| ||||
Final index | 0.6 | 0.3 | 0.5 | 0.8 |
Final index | 0.9 | 0.5 | 0.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
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
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"
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.