Forum Discussion
Merge two rows based on conditions using Power Query
Hi,
I'm hoping that somebody on here can help me as I've been unable to find a way to do this in Power Query and don't know enough about M Code to be able to obtain my desired solution.
I've attached a sample picture showing my starting data, in the left-hand table, as imported into Power Query and the desired result in the table in the right-hand table.
There are some rows that should be transferred directly (example being the row dated 23/07/2018) but some rows need to be combined (example being 20/07/2018 with the row below). I've tried to show this with the arrows.
The best explanation that I can give is that the rows that need to be combined have, in the first row that needs to be combined, a blank cell in both [Column 5] and [Column 6] and a blank on the second row in [Column 2]. Rows that should be transposed directly have data in [Column 2] and [Column 5] and OR [Column 6] exclusively - not both.
I hope that I've been clear enough and that one of you guys can help me with this frustrating puzzle. If it can be done using the standard editor I'd be interested to see how but I'd like to see the M Code so that I can learn how to achieve this too!
Hi Anonymous ,
please check the enclosed file for the solution as well:// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}, {"Column2", type text}, {"Column3", type text}, {"Column4", type datetime}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "EntryIndex", each if [Column5] = null and [Column6] = null then null else [Index]), #"Filled Up" = Table.FillUp(#"Added Custom",{"EntryIndex"}), #"Replaced Value1" = Table.ReplaceValue(#"Filled Up",null, each [Index],Replacer.ReplaceValue,{"EntryIndex"}), #"Filled Down" = Table.FillDown(#"Replaced Value1",{"Column1", "Column2"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"EntryIndex"}, {{"Column1", each List.Min([Column1]), type nullable datetime}, {"Column2", each List.Min([Column2]), type nullable text}, {"Column5", each List.Sum([Column5]), type nullable number}, {"Column6", each List.Sum([Column6]), type nullable number}, {"Column3", each Text.Combine(_[Column3], " ")}}), #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"Column5", "Column6"}), #"Inserted Addition" = Table.AddColumn(#"Replaced Value", "Value", each [Column6] - [Column5], type number), Custom1 = fnRunningTotal( #"Inserted Addition", "EntryIndex", "Value") in Custom1
4 Replies
Hi Anonymous ,
please check the enclosed file for the solution as well:// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}, {"Column2", type text}, {"Column3", type text}, {"Column4", type datetime}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "EntryIndex", each if [Column5] = null and [Column6] = null then null else [Index]), #"Filled Up" = Table.FillUp(#"Added Custom",{"EntryIndex"}), #"Replaced Value1" = Table.ReplaceValue(#"Filled Up",null, each [Index],Replacer.ReplaceValue,{"EntryIndex"}), #"Filled Down" = Table.FillDown(#"Replaced Value1",{"Column1", "Column2"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"EntryIndex"}, {{"Column1", each List.Min([Column1]), type nullable datetime}, {"Column2", each List.Min([Column2]), type nullable text}, {"Column5", each List.Sum([Column5]), type nullable number}, {"Column6", each List.Sum([Column6]), type nullable number}, {"Column3", each Text.Combine(_[Column3], " ")}}), #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"Column5", "Column6"}), #"Inserted Addition" = Table.AddColumn(#"Replaced Value", "Value", each [Column6] - [Column5], type number), Custom1 = fnRunningTotal( #"Inserted Addition", "EntryIndex", "Value") in Custom1Hi Anonymous ,
you picture gives a good explanation, but it would be best if you would have included sample data as well.
So without I can just give you instructions about the steps:- Add an index column
- Check Column1 and Column2 and replace blanks by null
- Add a custom column: if [Column2] = null then null else [Index]
- Check Column1 and fill down values
- Multiply Column5 by -1
- Group on the column you created in step 3
- Use the SUM-Operation on the amount fields, MAX on Column1 and Column2 and add an ALL operation for the concatenation of the Description field
- In the ALL operation replace "each _" by "each Text.Combine(_[Column3], " ") - that will combine the fields of all rows within the group.
Hi ImkeF ,
Firstly, a huge thank you for your reply; I've added a sample table in a new thread (even adding this isn't straight-forward! And thanks for your guidance in how to do this too!), taken by importing a pdf into Power Query and then loading it directly as a table into Excel. The table is the export to Excel, loaded here directly. I'm going to follow the steps that you've described above to see if I can follow your explanation but I thought I would also load the sample table as I suspect I will do something incorrectly! Hopefully, with the "raw" data as taken from the pdf and loaded as a table, you might be able to walk me through the steps also so that I can actually see what I will have done incorrectly!
Hi ImkeF,
I believe I've managed to find the correct way to add the Sample Table; hope this might help you to help me!! Again huge thank you for your help!
Column1 Column2 Column3 Column4 Column5 Column6 Column7 19/07/2018 TFR Balance brought forward
111111 22222222841.38 INTERNAL TRANSFER 200 1041.38 20/07/2018 VIS KANOO 1041.38 FOREIGN EXCHA 205.39 835.99 23/07/2018 DD AVRO ENERGY LTD 79 756.99 25/07/2018 ATM CASH HANCO 01/07/2025 MITIE @10:33 60 696.99 26/07/2018 ATM CASH BANK JUL26 ASDA RY@08:53 60 636.99 27/07/2018 CR SALARY 4048.76 CR SALARY 994.29 5680.04 28/07/2018 ATM CASH BANK JUL28 ASDA RY@09:02 200 5480.04 30/07/2018 SO FRED BLOGGS FRED 30 5450.04 01/08/2018 DD INSURANCE C/L 27 DD INSURANCE C/L 10.15 DD TV LICENCE MBP 12.07 DD CREDIT CARD 2180.04 DD HOUSE INS 45.38 SO BROWN RJ&F SAVINGS 1050 SO BROWN R&F 77777777 10 SO BROWN R&F RJ BROWN 20 SO FRED R BROWN 999999999 50 SO VOYAGER EXPLORER FRED BLOGGS 8.75 2036.65 02/08/2018 DD MOBILE LTD 20.68 2015.97 03/08/2018 Balance carried forward 2015.97