Forum Discussion
Merge two rows based on conditions using Power Query
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
Hi 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