How to Merge 2 rows into 1 in Power Query

Hi lennox25 ,

According to your description, here are my steps you can follow as a solution.

(1) Make a copy of the original table. Add an index column on the original table sorted by ascending order of id. Then add an index column on the copied table sorted by descending order of the id.

(2) Merge the two tables.

(3) Expand the last column of the merged table, then filter for values where index column = 1, then delete both index columns. You can view the steps in the step by step column on the right for your reference pbix below.

let
    Source = Table.NestedJoin(Table, {"Index"}, #"Table (2)", {"Index"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(Source, "Table (2)", {"ID", "Serial No", "Date", "Result", "Next Visit Due", "Visit Code", "Index"}, {"Table (2).ID", "Table (2).Serial No", "Table (2).Date", "Table (2).Result", "Table (2).Next Visit Due", "Table (2).Visit Code", "Table (2).Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table (2)", each ([#"Table (2).Index"] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Table (2).Index"})
in
    #"Removed Columns"

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.