Looping through columns in table

[Meindert]

Hi everyone,

I have the following problem.

My datasource, an Excel file, exists out of 101columns. As from column 20 I want to merge 2 columns at the time. 

So the data in column 20 and column 21 needs to be merged in a new column 20, with || as seperator.

Column 22 and column 23 needs to be merged in a new column 22, again with || as the seperator....till column 100 and column 101...

I started it by manually merging 2 columns, but my Query becomes so long and I was wondering if there isn't a faster, more elegant way to do this by creating a loop in M.

The amount of columns is fixed and the starting column also.

Thanks for anyone who can guide me to a possible solution.

[Meindert · Accepted solution]

Hi,

I've been testing around, after a friend told me a way to incoorporate loops in M and this is what I found myself.

MergedColumns = List.Accumulate(

        {7..40}, #"Changed Type",(state,current) 

        => Table.CombineColumns(state,

                                {"Column" & Number.ToText(current*2-1), "Column" & Number.ToText(current*2) },

                                Combiner.CombineTextByDelimiter("||",QuoteStyle.None),

                                "Column"& Number.ToText(current*2-1))

        )

And this works. It merges all columns between number 13 and 80 and gives them correct names.

Cheers

Meindert