Looping through columns in table

Question

Hi everyone,&nbsp;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.&nbsp;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.&nbsp;Thanks for anyone who can guide me to a possible solution.&nbsp;&nbsp;

meindert · Accepted Answer

Hi,&nbsp;I've been testing around, after a friend told me a way to incoorporate loops in M and this is what I found myself.&nbsp;MergedColumns = List.Accumulate(&nbsp; &nbsp; &nbsp; &nbsp; {7..40}, #"Changed Type",(state,current)&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; =&gt; Table.CombineColumns(state,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {"Column" &amp; Number.ToText(current*2-1), "Column" &amp; Number.ToText(current*2) },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Combiner.CombineTextByDelimiter("||",QuoteStyle.None),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Column"&amp; Number.ToText(current*2-1))&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp;And this works. It merges all columns between number 13 and 80 and gives them correct names.&nbsp;CheersMeindert&nbsp;&nbsp;&nbsp;

Forum Discussion

Looping through columns in table

1 Reply

Related content

ELT JSON Semi Structured Loop

Table.SelectRows Custom Column Looping Variable

ETL Options with files loop (powerquery or free?)

ETL API Loop with multiple paramaters

Loop Function