Forum Discussion
New Table to Create Categorize Existing Columns
Hello sirs.. I might not have described title clearly.. what I want to do is converting following data;
| Date | Unit1 | Unit2 | Unit3 |
| 01.01.2020 | 100 | 200 | 300 |
| 02.01.2020 | 150 | 300 | 250 |
into -->
| Date | Unit | Value |
| 01.01.2020 | 1 | 100 |
| 02.01.2020 | 1 | 150 |
| 01.01.2020 | 2 | 200 |
| 02.01.2020 | 2 | 300 |
| 01.01.2020 | 3 | 300 |
| 02.01.2020 | 3 | 250 |
How can I do it ?
Thanks
Anonymous , Unpivot and then either split or replace Unit with empty string
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/https://docs.microsoft.com/en-us/powerquery-m/text-replace
Or right click on the new column and use replace unit with empty
2 Replies
Anonymous , Unpivot and then either split or replace Unit with empty string
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/https://docs.microsoft.com/en-us/powerquery-m/text-replace
Or right click on the new column and use replace unit with empty
Hi Anonymous
Use this code or this PBIX file
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyMDJR0lQwMQaQQmjYFkrA5QgRGyAlOYFFAZkB0bCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Unit1 " = _t, Unit2 = _t, Unit3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Unit1 ", Int64.Type}, {"Unit2", Int64.Type}, {"Unit3", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.AfterDelimiter(_, "Unit"), type text}}), #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{"Attribute", Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Attribute", Order.Ascending}, {"Date", Order.Ascending}}) in #"Sorted Rows"Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
