Collapse data to single

[qasimawan]

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!

[lbendlin · Accepted solution]

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.

[SamWiseOwl · Accepted solution]

Hi @qasimawan 
Could you use Group By to achieve what you are looking for

[Omid_Motamedise · Accepted solution]

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"

[watkinnc]

Yes, just hold control and click on the first three rows, and then the last row, then click, remove duplicates.

--Nate

[SamWiseOwl · Accepted solution]

Hi @qasimawan 
Could you use Group By to achieve what you are looking for

[lbendlin · Accepted solution]

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.