Transform multiple columns into a single row

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

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.

[amitchandak]

@Anonymous , How doubt this unpivot twice case, if so refer

https://kohera.be/blog/power-bi/how-to-unpivot-twice/ 

[parry2k]

@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.⚡