Forum Discussion

Frequent Visitor

1 year ago

Solved

Collapse data to single

Hello community!

I have the following dataset and I want to collapse this to a single row:

How this looks like in Power Query:

First Name	Last Name	Employee Number	Expiry Date	Date Awarded	Training 1	Training 2	Training 3	Training 4	Training 5
Joe	Bloggs	12345678			Required		Required
Joe	Bloggs	12345678		43963
Joe	Bloggs	12345678	45685	45320
Joe	Bloggs	12345678	45749	45384
Joe	Bloggs	12345678	45758	44663
Joe	Bloggs	12345678	45772	44677				Match
Joe	Bloggs	12345678	45775	44680
Joe	Bloggs	12345678	45776	45411					Match
Joe	Bloggs	12345678	45815	44720
Joe	Bloggs	12345678	46050	45320
Joe	Bloggs	12345678	46089	44994
Joe	Bloggs	12345678	46139	45044		Match
Joe	Bloggs	12345678	46202	45107
Joe	Bloggs	12345678	46271	45176

How I want this to look:

First Name	Last Name	Employee Number	Expiry Date	Date Awarded	Training 1	Training 2	Training 3	Training 4	Training 5
Joe	Bloggs	12345678			Required	Match	Required	Match	Match

Can I do this using the user interface?

Thanks ever so much for your help!

1. Your data is not in a usable format. Unpivot it to make it usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZKxCsIwEIZfpWTucJdc7pLVUXBxLR1ESxWEotb3t+QUpRQa4hD+ZPh+jvvSNGY7dKY2m+vQ94/pgtaRZwnTtXqffXd7Xu7daflZmbZeayEX2f00VpnslMGndBaKeKGofKAy3qckLpxfxCovMmN3h/F4zuzw2hEKdyCckhAXeJ1jrSOgziBlHhg8/OORISSPFGORR0an/wDow3/3n9thIbn0CHOXmbyg8snHAt++AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, #"Employee Number" = _t, #"Expiry Date" = _t, #"Date  Awarded" = _t, #"Training 1" = _t, #"Training 2 " = _t, #"Training 3" = _t, #"Training 4" = _t, #"Training 5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Expiry Date", Int64.Type}, {"Date  Awarded", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Expiry Date", type date}, {"Date  Awarded", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date  Awarded", "Expiry Date", "Employee Number", "Last Name", "First Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " "))
in
    #"Filtered Rows"

2. Once you did that you can let the visual do the rest of the work.

Joe Bloggs.pbix22 KB

SamWiseOwl
1 year ago
Hi qasimawan
Could you use Group By to achieve what you are looking for

Omid_Motamedise

Just copy the below code and past it into advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZKxCsIwEIZfpWTucJdc7pLVUXBxLR1ESxWEotb3t+QUpRQa4hD+ZPh+jvvSNGY7dKY2m+vQ94/pgtaRZwnTtXqffXd7Xu7daflZmbZeayEX2f00VpnslMGndBaKeKGofKAy3qckLpxfxCovMmN3h/F4zuzw2hEKdyCckhAXeJ1jrSOgziBlHhg8/OORISSPFGORR0an/wDow3/3n9thIbn0CHOXmbyg8snHAt++AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, #"Employee Number" = _t, #"Expiry Date" = _t, #"Date  Awarded" = _t, #"Training 1" = _t, #"Training 2 " = _t, #"Training 3" = _t, #"Training 4" = _t, #"Training 5" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"First Name", "Last Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " ")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value", each Text.Combine(_,", "))
in
    #"Pivoted Column"

4 Replies

Super User

1. Your data is not in a usable format. Unpivot it to make it usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZKxCsIwEIZfpWTucJdc7pLVUXBxLR1ESxWEotb3t+QUpRQa4hD+ZPh+jvvSNGY7dKY2m+vQ94/pgtaRZwnTtXqffXd7Xu7daflZmbZeayEX2f00VpnslMGndBaKeKGofKAy3qckLpxfxCovMmN3h/F4zuzw2hEKdyCckhAXeJ1jrSOgziBlHhg8/OORISSPFGORR0an/wDow3/3n9thIbn0CHOXmbyg8snHAt++AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, #"Employee Number" = _t, #"Expiry Date" = _t, #"Date  Awarded" = _t, #"Training 1" = _t, #"Training 2 " = _t, #"Training 3" = _t, #"Training 4" = _t, #"Training 5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Expiry Date", Int64.Type}, {"Date  Awarded", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Expiry Date", type date}, {"Date  Awarded", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date  Awarded", "Expiry Date", "Employee Number", "Last Name", "First Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " "))
in
    #"Filtered Rows"

2. Once you did that you can let the visual do the rest of the work.

Joe Bloggs.pbix22 KB

SamWiseOwl
Super User
1 year ago
Hi qasimawan
Could you use Group By to achieve what you are looking for
Anonymous
Not applicable
1 year ago
Yes, just hold control and click on the first three rows, and then the last row, then click, remove duplicates.

--Nate

Omid_Motamedise

Super User

Just copy the below code and past it into advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZKxCsIwEIZfpWTucJdc7pLVUXBxLR1ESxWEotb3t+QUpRQa4hD+ZPh+jvvSNGY7dKY2m+vQ94/pgtaRZwnTtXqffXd7Xu7daflZmbZeayEX2f00VpnslMGndBaKeKGofKAy3qckLpxfxCovMmN3h/F4zuzw2hEKdyCckhAXeJ1jrSOgziBlHhg8/OORISSPFGORR0an/wDow3/3n9thIbn0CHOXmbyg8snHAt++AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, #"Employee Number" = _t, #"Expiry Date" = _t, #"Date  Awarded" = _t, #"Training 1" = _t, #"Training 2 " = _t, #"Training 3" = _t, #"Training 4" = _t, #"Training 5" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"First Name", "Last Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " ")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value", each Text.Combine(_,", "))
in
    #"Pivoted Column"

Related content