Log In
Register
Home
Power Query
October 7, 2022
Offline
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
See Answer
Trusted Members
Moderators
January 31, 2022
Offline
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
Answers Post
Power Query
October 7, 2022
Offline
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
Trusted Members
Moderators
January 31, 2022
Offline
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.
Power Query
October 7, 2022
Offline
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
Trusted Members
Moderators
January 31, 2022
Offline
I believe you forgot to press the "Start upload" button before you submitted your reply. Please try again.
Power Query
October 7, 2022
Offline
You are right! First time submitting attachments here on the forum and missed that step 🙂 Thank you.
Trusted Members
Moderators
January 31, 2022
Offline
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.
November 8, 2013
Offline
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"}})),
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"
Power Query
October 7, 2022
Offline
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!
1 Guest(s)
