Need to unpivot/transpose table keeping pairs of columns together

[Anonymous]

I sure I'm missing something really obvious here...

The user has tracked Name, Date, Weight for a varying number of weeks in Excel.

Each Name takes one row only and has many Date/Weight columns.

So, sadly my headings are Name, Date1, Weight1, Date2, Weight2...  for a couple of rows, there are now columns as far as Date64 & Weight64

To make this data usable, I need 3 columns - Name, Date, Weight.

How would anyone attack this?

[v-jingzhang]

Hi @Anonymous 

Daniel's solution works perfectly with only one step. I cannot think of such smart codes!

I think of a solution which requires several steps. If you have interest, you can create a blank query, open its advanced editor and clear the code there, then paste below code into it and save to see detailed results of every step. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lEyMjAy0jfUNwQyjWFcIzDXFMY1BnMtlWJ1opWSklD1mKDqMUfVY6EUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date1 = _t, Weight1 = _t, Date2 = _t, Weight2 = _t, Date3 = _t, Weight3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date1", type date}, {"Weight1", Int64.Type}, {"Date2", type date}, {"Weight2", Int64.Type}, {"Date3", type date}, {"Weight3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 0.5, Int64.Type),
    #"Rounded Down" = Table.TransformColumns(#"Added Index",{{"Index", Number.RoundDown, Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Rounded Down", each Text.StartsWith([Attribute], "Date")),
    Custom1 = Table.SelectRows(#"Rounded Down", each Text.StartsWith([Attribute], "Weight")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Name", "Index"}, Custom1, {"Name", "Index"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Value"}, {"Filtered Rows.Value"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Filtered Rows",{"Name", "Value", "Filtered Rows.Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Value", "Date"}, {"Filtered Rows.Value", "Weight"}})
in
    #"Renamed Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[wdx223_Daniel · Accepted solution]

NewStep=#table({"Name","Date","Weight"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(List.Skip(_),2),(x,y)=>{x{0}}&y))

[JKH66]

Thanks Daniel - that one line did everything I needed.  (unfortunately, I had to create a new account to sign in, so I'm not sure that I still have access rights to accept this as the solution - but it is absolutely perfect.)