Add n steps for n optional inputs of a function

[freelensia]

I have wrote this function that combines Col1, Col2, Col3 from SourceTbl into single Col with a common IDCol:

(SourceTbl as table, IDCol as text, Col as text, Col1 as text, Col2 as text, Col3 as text) =>
let
KeepOnlyCol1 = Table.SelectColumns(SourceTbl,{Col1, IDCol}),
RenameCol1 = Table.RenameColumns(KeepOnlyCol1,{{Col1, Col}}),
KeepOnlyCol2 = Table.SelectColumns(SourceTbl,{Col2, IDCol}),
RenameCol2 = Table.RenameColumns(KeepOnlyCol2,{{Col2, Col}}),
KeepOnlyCol3 = Table.SelectColumns(SourceTbl,{Col3, IDCol}),
RenameCol3 = Table.RenameColumns(KeepOnlyCol3,{{Col3, Col}}),
AppendCols = Table.Combine({RenameCol1, RenameCol2, RenameCol3})
in
AppendCols

How do modify this function so that:

1. It allows for col4, col5, ... coln (as many as the user inputs)

2. With each col, it reproduces the 2 steps:

KeepOnlyColn = Table.SelectColumns(SourceTbl,{Coln, IDCol}),
RenameColn = Table.RenameColumns(KeepOnlyColn,{{Coln, Col}}),

Then add the end append all output tables together:

AppendCols = Table.Combine({RenameCol1, RenameCol2, RenameCol3, ... RenameColn})

[ImkeF · Accepted solution]

Hi @freelensia ,

I believe sth like this should work:

(SourceTbl as table, IDCol as text, Col as text, ColNames)) =>
let
Transform = List.Transform(ColNames, each Table.RenameColumns(
						Table.SelectColumns(SourceTbl, {_, IDCol}), 
						{_, Col}) 
						)
AppendCols = Table.Combine(Transform)
in
AppendCols

Where ColNames has to be filled in as list. So the users just have to pass a list with column names into the last function parameter.

[freelensia]

Thanks @ImkeF for this solution. Could you help explain the logic of it?
I can see that the query will go through each item in ColNames List and transform it in some way. But I don't understand the logic of the Rename. What is the meaning of _ here?

[ImkeF]

Just recognized that my answer on this question was lost...

So here it comes again:

The underscore stands for each element in the List of ColumnNames. 

As this is iterated through, you need an expression in the syntax that represents each element during the iteration.

[v-piga-msft]

Hi @freelensia ,

I'm not understand your requirement completely.

I'm not good at Power Query. Hope @ImkeF could have ideas for your scenario.

Best Regard,

Cherry