Merging Columns

[Hsilva]

Hi Community;

I have an excel report (which comes from MYOB) where some columns have header and others do not. Sometimes the data splits in two columns, so I have to merge these two columns in order to get the 100% of the detail. The problem is that the position of the columns change from one report to another, for example:

Report A (total quantity = 15 in two columns)

Item	Column2	Column3	Quantity
A	null	10
B	null		5

Report B (total quantity =15 in one column)

Item	Column2	Quantity
A	null	10
B	null	5

I my first query I did Promote headers, then merge Columns "Quantity" with "Column3" to get the total = 15.

In the second report, I do not need to merge, but when merging I got the error "Column3 wasn't found"

Any suggestions would be much appreciated.

Regards

Humberto

[Anonymous]

Hi @Hsilva ,

Based on this:

In the second report, I do not need to merge, but when merging I got the error "Column3 wasn't found"

You may have referenced a no-existing column . So please navigate to the wrong Applied step.

And If possible, please share the whole M syntax to help us clarify your scenario.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hsilva]

Hi Vijay, thank you very much for your prompt answer.

Apologies I think I made a mistake simplifying the example. The code works if the file has one empty column, however it has many and Ialso applyed few steps to get the header.

If you can have a look to the attached file and give me some advice would be great.

Thank you again for your help.

Humberto

https://primaryhealth-my.sharepoint.com/:x:/g/personal/josef_silva_primaryhealthcare_com_au/ERsa24vI... 

[Vijay_A_Verma]

Use following m code for dynamic merging of columns (Solution demonstrating this is uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuS9iB3OYRgx-5Bjb?e=0CkeKS )

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ListOfColumns = List.RemoveFirstN(Table.ColumnNames(Source),1),
    TransformListOfColumns = List.Transform(ListOfColumns, each {_, type text}),
    #"Transform Column Types" = Table.TransformColumnTypes(Source, TransformListOfColumns),
    #"Combine Columns" = Table.CombineColumns(#"Transform Column Types",ListOfColumns,Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Final Quantity"),
    #"Result" = Table.TransformColumnTypes(#"Combine Columns", {"Final Quantity", type number})
in
    Result