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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
stuartrevnell
New Member

Removing columns using a named range - columns not recognised

Hi there - new to this forum (and fairly new to Power Query too...).
 
I have a dataset download I regularly have to check for errors, and I'm trying to automate it. When the dataset comes out of our system it has loads of columns that are surplus, so I want to delete them, but sometimes other users may want them. So, what I'm trying to do is create a named range in Excel called 'surplusColumns', which contains a dynamically generated list of these, depending on who wants what, which will then be used in the Table.RemoveColumns function to tell PQ which ones to removed.
 
Here is my named range 'surplusColumns' - originally this was dynamically generated using TEXTJOIN, but the screenshot below just shows two manually entered column names.
stuartrevnell_8-1683305258781.png

In the Advanced Editor, I havea step name 'SurplusCols' set to read from this named range...

 
 
stuartrevnell_10-1683305446188.png

 

 

...and I can see that this is working:
 
stuartrevnell_11-1683305463007.png

 

 

In the step where I want to remove these columns, I'm using Table.RemoveColumns, and passing the 'SurplusCols' name the function, but it's saying that it can't find these two columns:
 
 
stuartrevnell_12-1683305476840.png

 

 

If I put the MissingField,Ignore condition in though, I can see these two columns are definitely still there:
 
stuartrevnell_13-1683305486743.png

 

 

I think this is something to do with apostrophes, as I change the named range 'surplusColumns' to just reference one column, without apostrophes..
 
stuartrevnell_14-1683305502183.png

 

 

...
The 'UFResourceId' column disappears - red shows where it should be:
 
 
stuartrevnell_15-1683305514300.png

 

What got me going down this track of concatenating with apostrophes is that this is the syntax for removing multiple columns:
 
stuartrevnell_16-1683305528779.png

 

So I am basically trying to dynamically generate what's in yellow here, containing multiple column names, from a concatenated list of field names in the main Excel, delimited by ", ".
 
From what I can see, there's something about escaping apostrophes with two apostrophes in Power Query, which I've tried (possibly incorrectly :-)) - can anyone let me know where I'm going wrong, please?
 
Many thanks!
 
Stuart
2 ACCEPTED SOLUTIONS
m_dekorte
Super User
Super User

Hi @stuartrevnell 

 

When you're deleting columns with the help of the UI, it's generating a hard coded list with text values for each column name. In the M language to create a text value you need to enter your sting in between quotes, that's why you see them there in the code. 

 

But when you're bringing in text values, there is no need for quotes.

Note that with your method if you're supplying Power Query a single string containing mutiple column names, you'll need to split that up into a list containing separate list items. For example: Text.Split(SurplusCols, ", ")

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

View solution in original post

stuartrevnell
New Member

Hi @m_dekorte - thanks so much for your response...that's working!

For reference, here's the working solution:

1) Named range 'surplusColumnns' in Excel, with three columnns reference - Column A, Column B and Column C

stuartrevnell_0-1683626303124.png

2) Definition of 'SurplusCols' parameter in the step editor, showing the three columns pulling through from the named range:

stuartrevnell_1-1683626458883.png

3) 'Remove all surplus columns' step in the step editor, showing how I've used the 'SurplusCols' paramater in the 'Table.RemoveColumns' function to dynamically remove the columns referenced in the named range:

stuartrevnell_2-1683626540735.png

Really appreciate your help on this one!

Stuart

View solution in original post

2 REPLIES 2
stuartrevnell
New Member

Hi @m_dekorte - thanks so much for your response...that's working!

For reference, here's the working solution:

1) Named range 'surplusColumnns' in Excel, with three columnns reference - Column A, Column B and Column C

stuartrevnell_0-1683626303124.png

2) Definition of 'SurplusCols' parameter in the step editor, showing the three columns pulling through from the named range:

stuartrevnell_1-1683626458883.png

3) 'Remove all surplus columns' step in the step editor, showing how I've used the 'SurplusCols' paramater in the 'Table.RemoveColumns' function to dynamically remove the columns referenced in the named range:

stuartrevnell_2-1683626540735.png

Really appreciate your help on this one!

Stuart

m_dekorte
Super User
Super User

Hi @stuartrevnell 

 

When you're deleting columns with the help of the UI, it's generating a hard coded list with text values for each column name. In the M language to create a text value you need to enter your sting in between quotes, that's why you see them there in the code. 

 

But when you're bringing in text values, there is no need for quotes.

Note that with your method if you're supplying Power Query a single string containing mutiple column names, you'll need to split that up into a list containing separate list items. For example: Text.Split(SurplusCols, ", ")

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors