Forum Discussion

freelensia's avatar
freelensia
Advocate II
7 years ago
Solved

Add n steps for n optional inputs of a function

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})
Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago

    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.

4 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

    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's avatar
      freelensia
      Advocate II
      7 years ago

      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's avatar
        ImkeF
        Community Champion
        7 years ago

        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's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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