Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

5 REPLIES 5
ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

v-yuta-msft
Community Support
Community Support

@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
Not applicable

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 🙂

 

 

Old post I know. But in case someone in the future has the same problem.

It looks like you are dealing with financial data coming from Excel. If that is the case, then select a cell in the Excel data, create a table (CTRL+T), name the table (under the Table Design menu), and then have Power Query consume the table, not the worksheet.

@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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors