Power Query Merging and Transforming Tables

[Nestmoxica]

I have to track attendance every month and would like to automate the process using power query. Every month I receive a list that has names and attendance. I want to merge the data and compress it so that the data doesn't stay in a long format.. See pictures in attachment so you have an idea what I mean. So far, I have managed to look like the left side. I want it to look like the right side. Thank you!

[wdx223_Daniel]

NewStep=Table.Group(PreviousStep,{"First Name","Last Name"},List.Transform(List.Skip(Table.ColumnNames(PreviousStep),2),(x)=>{x,each Text.Combine(Table.Column(_,x))}))

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQrOzSzJANKRQKwUqxOtFJKfC2SGZJTmJiELeyXmpQLZLvmpKKKohoClMFRDRGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, Day1 = _t, Day2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"LastName", type text}, {"Day1", type text}, {"Day2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"FirstName", "LastName"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","",null,Replacer.ReplaceValue,{"Attribute", "Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Value] = "Y")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Pat