Merge / combine all columns of a table

[Goodkat]

Dear data enthusiasts,

Today I start building a new data model where in one process step I must merge all columns of the table. I did search the forum and the internet, but found only hints, which I tried to combine the right way, but I failed.

https://c.gmx.net/@324888734501700174/37A7kpkNTjuwYLy09Z8Gfg

In the attached example the recorded step is:

Merge = Table.CombineColumns(data,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"merge") 

But as the number of columns can vary, I thought to bring in the Table.ColumnNames of the previous step:

Merge = Table.CombineColumns(data, {Record.SelectFields(Table.ColumnNames(data))}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "merge")

But it does not work. If I try without the Record.SelecFields it also fails.

Has anyone a conclusive idea, how to achieve the flexible merger of all columns? Any good hint?

Thank you!

Best regards, Andreas

[Goodkat · Accepted solution]

Dear Power Query community,

I think with a night of sleep and proper thinking once again, I got it by myself:

If all columns shall be merged into one:

Merge = Table.CombineColumns(data, Table.ColumnNames(data), Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "merge")

If a merged column shall be added:

Add_Merge = Table.AddColumn(data, "cca", each Text.Combine(Record.FieldValues(Record.SelectFields(_, Table.ColumnNames(data))), " "), type text)

Best Regards, Andreas

[lbendlin]

Your source file is not in a suitable format.  You need to unpivot the data there before your merge step.

What is the purpose of the merge?

[Goodkat]

Dear Ibendlin,

the source data format is correct. I cannot unpivot before as the purpose is the following: When I have the content of all columns merged into one, I will put a formula evaluating this merged column for certain key strings that give me an idea, if the row is the one showing the years, months, quarter or content. This information is needed for the following steps, which then also include an unpivot.

But the merge of all columns has to happen in the given format.

Best regards, Andreas