Power Query - Merge columns if there is more than 1 column

[sara121]

Hi Friends,

I got stuck in this for days.
I have to convert the PDF file by page. As I transformed the page, the columns for the table are not preditable.
As seen from the example here below,
1) the debit value does not line up under the column header "DEBIT", instead, it appears under a new column. This new column header can be "Column 7" for this file. In the next PDF file, this may show under Column6.

2) on the DEBIT side, the header and value line up properly in this example. when open another file, it may have extra blank column displayed between the CREDIT and DATE column.

Is there way to Merge the columns if there is more than 1 coloumn shown before DATE column else do nothing?

Thanks so much!

 

[jdellasala]

Maybe I'm not understanding the entire problem. If the DEBIT data is spread out over only two columns, and even if other data like the CREDIT column has the same problem

Book1
	A	B	C	D	E	F
1	DEBIT	Column7	DEBIT DATE	Column8	CREDIT	CREDIT DATE
2		$ 1,000.00	2/1		$ 2,000.00	2/1
3	$ 1,000.00		2/2		$ 2,000.00	2/2
4		$ 1,000.00	2/3	$ 2,000.00		2/3
Sheet2

A simple merge will clean them up:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"DEBIT", type number}, {"Column7", type number}, {"DEBIT DATE", type date}, {"Column8", type number}, {"CREDIT", type number}, {"CREDIT DATE", type date}}),
    MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(ChangedType, {{"DEBIT", type text}, {"Column7", type text}}, "en-US"),{"DEBIT", "Column7"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Debit.1"),
    MergedColumns1 = Table.CombineColumns(Table.TransformColumnTypes(MergedColumns, {{"Column8", type text}, {"CREDIT", type text}}, "en-US"),{"Column8", "CREDIT"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Credit.1"),
    ChangedType1 = Table.TransformColumnTypes(MergedColumns1,{{"Debit.1", type number}, {"Credit.1", type number}})
in
    ChangedType1

Resulting in this

Book1
	H	I	J	K
1	Debit.1	DEBIT DATE	Credit.1	CREDIT DATE
2	$ 1,000.00	2/1	$ 2,000.00	2/1
3	$ 1,000.00	2/2	$ 2,000.00	2/2
4	$ 1,000.00	2/3	$ 2,000.00	2/3
Sheet2

The new merged columns were automatically named and can be easily changed as needed right in the Code bar. The only non UI things I did was rename the steps removing the spaces to make the code easier to read.