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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.