Forum Discussion
Merge two rows based on conditions using Power Query
- 4 years ago
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 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 22222222 | 841.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 |