Report with fields spread across two rows

[Anonymous]

I have a report in excel that has 61 fields.  The report is formatted to have the fields on two rows.  I'm assuming because it's easier to view.  For additional context it is a vendor report with vendor code and then 60 other different fields.

However,  I need to sort and pivot the data.  So, I need to get a vendor's data all on one row.  The primary key is on the first row but not the second.  I have a very manually intensive solution that takes me ~ 9 minutes with a sheet that has 1500 rows.  If I had a report with 15-20,000 rows it probably would take 45 minutes. 

Is there an elegant solution in power query?

I also don't have an odbc connection or I would just write a sql query and rip it out.

[edhans · Accepted solution]

See this. Look at the steps as I go through it. Put this in a blank query.

EDIT: You need to confirm data types after the transofrmation as the last step. I just noticed my Date below is showing as text, not a date. That is becusae the column it was in originally had a text value too. So check every field to ensure the data loads correctly.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUXJMBAIgnQQEQMrQyFgpVidaCcTUN9Q3MjAyADJ984tSFVISS0AKDU1UwSpAqhOLU9JgGIkLZBobm5iawkwy0jeCmVRckpmTo5CLbJ4F0LxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t, Col1 = _t, Col2 = _t, Col3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Col1", type text}, {"Col2", type text}, {"Col3", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Col1", "Col2", "Col3"}, {"Added Index1.Col1", "Added Index1.Col2", "Added Index1.Col3"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Added Index1",{"Key", "Col1", "Col2", "Col3", "Added Index1.Col1", "Added Index1.Col2", "Added Index1.Col3"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Key] <> ""))
in
    #"Filtered Rows"

Becomes this (not all columns showing...)

If that isn't what you want, see links below to send us some good data to play with.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum