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

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

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.

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

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

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

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.

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"}})),

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!