Removing columns using a named range - columns not recognised

[stuartrevnell]

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.

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

...and I can see that this is working:

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:

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

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

...

The 'UFResourceId' column disappears - red shows where it should be:

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

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

[m_dekorte · Accepted solution]

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!

[stuartrevnell · Accepted solution]

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

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

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:

Really appreciate your help on this one!

Stuart

[m_dekorte · Accepted solution]

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!