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
In the Advanced Editor, I havea step name 'SurplusCols' set to read from this named range...
...
Solved! Go to Solution.
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!
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
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
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
67 | |
25 | |
18 | |
12 |