Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Power Query: Referencing dynamic Column Names

Hi All

 

I have some trouble with dynamic names. My input sheet will change names from one month to the other. I know I can use Table.ColumnNames, but I can't get it to work when I have multiple columns.

 

So basically my question is: How do I change:

 

= Table.RemoveColumns(#"Promoted Headers1",{"jul-17", "aug-17", "sep-17", "okt-17", "nov-17", "dec-17", "jan-18", "feb-18", "mar-18", "apr-18", "maj-18", "jun-18", "jul-18", "aug-18", "sep-18", "okt-18", "nov-18", "dec-18", "jan-19", "feb-19", "01-03-2019", "a", "b", "c", "Total", "Total_1", "Column106", "Column107", "Column108", "Column109", "Column110"})

 

To a statement that looks at the column reference instead of the dynamic name?

 

Thanks

Need Help

5 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous ，

     

    Anonymous wrote:

    Hi All

     

    I have some trouble with dynamic names. My input sheet will change names from one month to the other. I know I can use Table.ColumnNames, but I can't get it to work when I have multiple columns.

     

    So basically my question is: How do I change:

     

    = Table.RemoveColumns(#"Promoted Headers1",{"jul-17", "aug-17", "sep-17", "okt-17", "nov-17", "dec-17", "jan-18", "feb-18", "mar-18", "apr-18", "maj-18", "jun-18", "jul-18", "aug-18", "sep-18", "okt-18", "nov-18", "dec-18", "jan-19", "feb-19", "01-03-2019", "a", "b", "c", "Total", "Total_1", "Column106", "Column107", "Column108", "Column109", "Column110"})

     

    To a statement that looks at the column reference instead of the dynamic name?

     

    Thanks

    Could you show some sample data and give the expected result?

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I have a table with the below columns + some more columns.. the latter I want to keep and the one in the below code I want to remove. It works perfectly.. only problem is that when I update next time the first column is no longer "jul-17" but "aug-17" and that will give me an error since it can't find the "jul-17". So I need to be able to refer to the column position instead of the name for it to work in future uploads. I can do this with the Table.columnNames function, but I can only get that to work when I refer to 1 column and not multiple columns... so I hope you or somebody in this fine forum can help :-)

       

       

      • v-yuta-msft's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Anonymous ,

         

        You may try Table.PromoteHeaders to make the first row as column heads of multiple columns

        Table.PromoteHeaders(table as table, optional options as nullable record) as table

         

        Community Support Team _ Jimmy Tao

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Wouldn't the command "Remove Other Columns" be the solution here?