• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to remove report columns and keep only columns that match the name from another list|Power Query|Excel Forum|My Online Training Hub

You are here: Home / How to remove report columns and keep only columns that match the name from another list|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryHow to remove report columns and ke…
sp_PrintTopic sp_TopicIcon
How to remove report columns and keep only columns that match the name from another list
Avatar
Dana Sarbulescu
Member
Members

Power Query
Level 0
Forum Posts: 8
Member Since:
October 7, 2022
sp_UserOfflineSmall Offline
1
November 12, 2022 - 6:26 am
sp_Permalink sp_Print

Hi,

Is there a way to write the M code in such a way that all columns that do not match the column names from a list are removed? Basically I want to keep only certain columns in my report and that list of columns might be different every month. E.g. I pulling a sales report over the last 13 months and I am always comparing revenue for current month versus previous month. The current month and previous month names will be different every month. If i define 2 parameters with the name of the current month and the previous month (which i manually update every month), is there a way to write the code to remove all columns that do not match the 2 column names?

Thanks,

Dana

sp_AnswersTopicSeeAnswer See Answer
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
2
November 12, 2022 - 6:56 pm
sp_Permalink sp_Print

Hi Dana,

Have a look at the attached example. I created a table with 10 columns containing some random numbers. Then a smaller table containing a list of column names that should be kept.

Connected to both with PQ and extracted only the columns included in the list.

At first you just select a few columns to keep via the user interface. That will create a code like:

= Table.SelectColumns(#"Removed Other Columns",{"Column3", "Column4"})

 

Replace the bold red part with the name of the list that contains the columns you want to keep creating the following code:

= Table.SelectColumns(#"Removed Other Columns", ColumnList)

See if you can follow the steps and come back here if you get stuck.

Riny

sp_AnswersTopicAnswer
Answers Post
Avatar
Dana Sarbulescu
Member
Members

Power Query
Level 0
Forum Posts: 8
Member Since:
October 7, 2022
sp_UserOfflineSmall Offline
3
November 15, 2022 - 1:47 pm
sp_Permalink sp_Print

Thank you so much! I was able to follow the steps and it worked.

Do you also have a suggestion for how to write the code to rename the remaining columns based on a rule? I always want to keep only the previous month and the current month columns in the report. The month names are defined in the list that was used to remove the other columns. But if I create a mapping table of let's say Sep22 = Previous month and Oct22 = Current month (which would be the same column names from the list), would I also be able to rename the column called Sep22 to "previous Month" and Oct22 to "current month"? 

I hope i will become as advanced as you one day so I can help other members 🙂 I am very grateful for everyone that takes time to respond to these forum threads.

 

Thanks,
Dana

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
4
November 15, 2022 - 2:30 pm
sp_Permalink sp_Print

That shouldn't be much of a problem.

Please upload a file that looks like your real one, before removing and renaming columns. Replace any confidential data with fake data. And indicate how the end result should be.

Then I don't have to make up an example myself that probably wouldn't resemble you real one.

Avatar
Dana Sarbulescu
Member
Members

Power Query
Level 0
Forum Posts: 8
Member Since:
October 7, 2022
sp_UserOfflineSmall Offline
5
November 17, 2022 - 2:37 pm
sp_Permalink sp_Print

Hi Riny,

 

Please see attached file. The tab All Time monthly revenue tracks sales for every month. Every month we add a new column for the revenue. Then on the Client revenue variance tab we have several queries that compare revenue changes for current month versus previous month. Now I know how to remove all the other columns and keep only the customer name and the 2 months in the file via the method you explained below. I would like in this example for the month of Sep22 to be renamed to "previous month" and Oct22 to "Current month" via a dynamic list. Because next time i run this report Oct22 will be "previous month" and Nov22 will be "current month". I now have to manually go in and update the M code for the 2 months. Look at the query "CustSalesPrevVsCurMo" and you'll in the last step the renaming, but the month names of the columns are hardcoded for the 2 months relevant for this month. Hopefully I explained this well enough 🙂

Thank you!

Dana

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
6
November 17, 2022 - 3:35 pm
sp_Permalink sp_Print

I believe you forgot to press the "Start upload" button before you submitted your reply. Please try again.

Avatar
Dana Sarbulescu
Member
Members

Power Query
Level 0
Forum Posts: 8
Member Since:
October 7, 2022
sp_UserOfflineSmall Offline
7
November 19, 2022 - 1:29 am
sp_Permalink sp_Print

You are right! First time submitting attachments here on the forum and missed that step 🙂 Thank you. 

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
8
November 19, 2022 - 7:27 pm
sp_Permalink sp_Print

Thanks for the file. I made some changes to make it dynamic, keeping most of the queries you had already prepared. Note I added a small table in a Parameters sheet with the three column names to be selected. The first two being the previous and current month and the thirs is the column called "Customer".

The trick to dynamically change whatever the previous and current months are called to "Previous month" and "Current month" is that, after selecting the columns to keep, you demote the headers. Then you will always have Column1, Column2 and Column3 that you can rename consistently. I trust you can follow these steps when you see them in action.

I believe this model works now, though personally I would probably have chosen different approach. Unpivoting the "All Time" data, and work with real dates and use Power Pivot to do all or most of the calculations and analysis. But I didn't pursue this any further.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
November 20, 2022 - 4:20 pm
sp_Permalink sp_Print sp_EditHistory

As an alternative, you can easily detect the last 2 columns using List.Select then using List.LastN, as in the example below for RevIncreaseCustomers.

Identifying the last 2 months and renaming are done in these steps, the rest of the query is Riny's work:

RemovedColumns = Table.SelectColumns(Source,{"Customer"} & List.LastN(List.Select(Table.ColumnNames(Source), each Text.Contains(_,"-")),2)),
Renamed = Table.RenameColumns(RemovedColumns,List.Zip({Table.ColumnNames(RemovedColumns),{"Customer","Previous month","Current month"}})),

let
Source = Excel.CurrentWorkbook(){[Name="AllTimeSlsCust"]}[Content],
RemovedColumns = Table.SelectColumns(Source,{"Customer"} & List.LastN(List.Select(Table.ColumnNames(Source), each Text.Contains(_,"-")),2)),
Renamed = Table.RenameColumns(RemovedColumns,List.Zip({Table.ColumnNames(RemovedColumns),{"Customer","Previous month","Current month"}})),
#"Added Custom" = Table.AddColumn(Renamed, "Revenue change", each [Current month]-[Previous month]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Current month", Currency.Type}, {"Revenue change", Currency.Type}, {"Previous month", Currency.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "% of change", each [Revenue change]/[Previous month]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"% of change", Percentage.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Exclude inactive", each if[Previous month]=0 and [Current month]=0 then "exclude" else "include"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Exclude inactive] = "include")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Revenue change] > 0),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Previous month] <> 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Exclude inactive"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Customer", Order.Ascending}})
in
#"Sorted Rows"
Avatar
Dana Sarbulescu
Member
Members

Power Query
Level 0
Forum Posts: 8
Member Since:
October 7, 2022
sp_UserOfflineSmall Offline
10
November 22, 2022 - 1:05 am
sp_Permalink sp_Print

Thank you so much! Ah, demoting the headers. I did not know I can do that and that solves a big problem for me for other queries as well. Awesome tips on the rest of the code as well. This will save me a lot of work on reporting. Thank you, thank you, thank you!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ruth Savage, Andy Kirby, Roy Lutke, Jeff Krueger
Guest(s) 7
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.