Forum Discussion
Transform multiple columns into a single row
I have data which is categorized as attributes of New and Used vehicles ie.(New SRPIS, Used SRPIS, New VDPS Used VDPS); by date and client. I need to perserve the date and client but transform the attribute columns so I have two rows (New and Used) with no repeating attribute columns. Row = New, columns = SRPIS, VDPS
Row = Used, columns = SRPIS, VDPS
In the visialization I need to show as a total and allow for slicing by New and Used.
Current:
| MODIFIED_DATE | CUSTOMER_ID | NEW_SRPIS | NEW_VDPS | NEW_EMAILS | USED_SRPIS | USED_VDPS | USED_EMAILS |
| 6/2/2020 0:00 | 100000120 | 3528 | 83 | 0 | 6637 | 105 | 0 |
| 5/2/2020 0:00 | 100000120 | 3037 | 63 | 0 | 5184 | 118 | 0 |
| 4/2/2020 0:00 | 100000120 | 2193 | 31 | 0 | 2854 | 53 | 1 |
| 3/2/2020 0:00 | 100000120 | 1689 | 30 | 1 | 3450 | 57 | 0 |
| 2/2/2020 0:00 | 100000120 | 2453 | 74 | 1 | 4067 | 98 | 1 |
| 1/2/2020 0:00 | 100000120 | 2205 | 35 | 1 | 3261 | 60 | 0 |
Needed
| MODIFIED_DATE | CUSTOMER_ID | New/Used | SRPIS | VDPS | EMAILS |
| 6/2/2020 0:00 | 100000120 | New | 3528 | 83 | 0 |
| 6/2/2020 0:00 | 100000120 | Used | 6637 | 105 | 0 |
| 4/2/2020 0:00 | 100000120 | New | 2193 | 31 | 0 |
| 4/2/2020 0:00 | 100000120 | Used | 5184 | 118 | 1 |
| 2/2/2020 0:00 | 100000120 | New | 2453 | 74 | 1 |
| 2/2/2020 0:00 | 100000120 | Used | 2854 | 53 | 0 |
Hi,
This M code works
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBLDoUgDEW38sJYk9sv9W3FuP9tSKuGGQxoIb2HE86zwXfwzmD8gD/QtkbIRZy9GMcoIWPLs7v0GrG6uLZBsDUBlfCPYBSaIxSToEsC05FhoZfAYUmwvKQHIEsAeRxlUoHRqJVJnwa8NtB6rOsLUHhmj5gGtAZwfZjYZ8CexfEYXDc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MODIFIED_DATE = _t, CUSTOMER_ID = _t, NEW_SRPIS = _t, NEW_VDPS = _t, NEW_EMAILS = _t, USED_SRPIS = _t, USED_VDPS = _t, USED_EMAILS = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MODIFIED_DATE", type datetime}, {"CUSTOMER_ID", Int64.Type}, {"NEW_SRPIS", Int64.Type}, {"NEW_VDPS", Int64.Type}, {"NEW_EMAILS", Int64.Type}, {"USED_SRPIS", Int64.Type}, {"USED_VDPS", Int64.Type}, {"USED_EMAILS", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"MODIFIED_DATE", "CUSTOMER_ID"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value") in #"Pivoted Column"Hope this helps.
3 Replies
- parry2k
Super User
Anonymous I have addressed a similar question here in my post. Take a look.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
- amitchandak
Anonymous , How doubt this unpivot twice case, if so refer
- Ashish_Mathur
Hi,
This M code works
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBLDoUgDEW38sJYk9sv9W3FuP9tSKuGGQxoIb2HE86zwXfwzmD8gD/QtkbIRZy9GMcoIWPLs7v0GrG6uLZBsDUBlfCPYBSaIxSToEsC05FhoZfAYUmwvKQHIEsAeRxlUoHRqJVJnwa8NtB6rOsLUHhmj5gGtAZwfZjYZ8CexfEYXDc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MODIFIED_DATE = _t, CUSTOMER_ID = _t, NEW_SRPIS = _t, NEW_VDPS = _t, NEW_EMAILS = _t, USED_SRPIS = _t, USED_VDPS = _t, USED_EMAILS = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MODIFIED_DATE", type datetime}, {"CUSTOMER_ID", Int64.Type}, {"NEW_SRPIS", Int64.Type}, {"NEW_VDPS", Int64.Type}, {"NEW_EMAILS", Int64.Type}, {"USED_SRPIS", Int64.Type}, {"USED_VDPS", Int64.Type}, {"USED_EMAILS", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"MODIFIED_DATE", "CUSTOMER_ID"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value") in #"Pivoted Column"Hope this helps.
