Merge two rows based on conditions using Power Query

[Anonymous]

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!

[ImkeF · Accepted solution]

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

[ImkeF]

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:

1. Add an index column
2. Check Column1 and Column2 and replace blanks by null 
3. Add a custom column: if [Column2] = null then null else [Index]
4. Check Column1 and fill down values
5. Multiply Column5 by -1
6. Group on the column you created in step 3
7. 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
8. In the ALL operation replace "each _" by "each Text.Combine(_[Column3], " ") - that will combine the fields of all rows within the group.

[Anonymous]

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!

[Anonymous]

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