Forum Discussion
How to Merge 2 rows into 1 in Power Query
This is a small sample of my data. I am working on last 2 visits. This is how the data currently is in the table.
What I would like to do (as need to do further work) is to have these 2 rows merged into one row as below.
Can anyone help please?
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.
2 Replies
Hi lennox25 -Input data of individual visits into a single row merged for each store with the latest two visits .
Please use the below Power Query editor script.
Transformations used, Sort, Group By, if else, AddColumns, Table Column Functions
Snapshot also attached FYR
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyMTVT0lEyMTYzB1JGhvoGlvpGBkbGQE5wYklmcVpickl+USWQa2wAlTMBcgyVYnWAug1NDC0Qui31wfJGQKZ7fn4Ksh6QeUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Store No" = _t, Date = _t, Result = _t, #"Next Visit Due" = _t, #"Visit Order" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Store No", Int64.Type}, {"Date", type text}, {"Result", type text}, {"Next Visit Due", type text}, {"Visit Order", Int64.Type}}),
SortedRows = Table.Sort(Source, {{"Store No", Order.Ascending}, {"Visit Order", Order.Ascending}}),
#"Grouped Rows" = Table.Group(SortedRows, {"Store No"}, {{"AllData", each _, type table [ID=nullable text, Store No=nullable text, Date=nullable text, Result=nullable text, Next Visit Due=nullable text, Visit Order=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([AllData],"ID"){0}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Table.RowCount([AllData])>1 then Table.Column([AllData],"ID"){1} else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.Column([AllData], "Date"){0}),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if Table.RowCount([AllData]) > 1 then Table.Column([AllData], "Date"){1} else null),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each Table.Column([AllData], "Result"){0}),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.5", each if Table.RowCount([AllData]) > 1 then Table.Column([AllData], "Result"){1} else null),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Custom.6", each Table.Column([AllData], "Next Visit Due"){0}),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Custom.7", each if Table.RowCount([AllData]) > 1 then Table.Column([AllData], "Next Visit Due"){1} else null),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "Custom.8", each Table.Column([AllData], "Visit Order"){0}),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Custom.9", each if Table.RowCount([AllData]) > 1 then Table.Column([AllData], "Visit Order"){1} else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom9",{"AllData"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Store No", "Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "ID"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Store No", "Custom.2", "Custom.1", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Custom.2", "Date1"}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns1",{"ID", "Store No", "Date1", "Custom.4", "Custom.1", "Custom.3", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns2",{{"Custom.4", "Result"}}),
#"Reordered Columns3" = Table.ReorderColumns(#"Renamed Columns2",{"ID", "Store No", "Date1", "Result", "Custom.6", "Custom.1", "Custom.3", "Custom.5", "Custom.7", "Custom.8", "Custom.9"}),
#"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns3",{{"Custom.6", "Next Visit Due1"}, {"Result", "Result1"}}),
#"Reordered Columns4" = Table.ReorderColumns(#"Renamed Columns3",{"ID", "Store No", "Date1", "Result1", "Next Visit Due1", "Custom.8", "Custom.1", "Custom.3", "Custom.5", "Custom.7", "Custom.9"}),
#"Renamed Columns4" = Table.RenameColumns(#"Reordered Columns4",{{"Custom.8", "Visit Order1"}, {"Custom.1", "ID2"}, {"Custom.3", "Date2"}, {"Custom.5", "Result2"}, {"Custom.9", "Visit Order2"}, {"Custom.7", "Next Visit2"}})
in
#"Renamed Columns4"Output as expected:
Above PQ works, please check.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!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.
