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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Community Champion
Community Champion

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.