Dynamic selection of columns and delete the others

[antotom]

Hello there,

I'm working on a project that consists on retrieving a file from a platform, and use 3 specific columns of it to fill a data dashboard.

Specifically, these columns have the following headers and features:

1. ID Code = it always has the same name, but not the same location
2. Company name = it always has the same name, but not the same location
3. YYYYMM = this column has a date in this format ("YYYYMM"), so a new one is added monthly and contains balances in $USD. It is always the last column, but of course the name changes monthly as a new month passes.

I need to keep just these 3 columns and delete the rest, but I'm struggling to find a solution. Would you have any advice please?

Thanks a lot in advance!

[artemus]

In that case you can use the custom step of :

Table.SelectColumns(previousStep, {"ID Code", "Company Name", List.Last(Table.ColumnNames(previousStep))})

However, I do not recommend you do it this way since having a column that changes its name each month will cause issues if you are using power bi (you might be able to get away with doing this in Excel). Instead, I would suggest you use the pivot operation on the ID Code and Company Name column (after removing all unneeded columns). This has the conquence of loading all the data with a new date column sperating each month.

[antotom]

Thanks a lot for that. And using the formula you reported above, what if I want the ID code and the last 4 columns?

[artemus]

If you select the 3 columns you can choose "Remove other columns". The M code for this is:

Table.SelectColumns(previousStep, {"ID Code", "Company name", "YYYYMM"})

or

previousStep[[ID Code], [Company name], [YYYYMM]]

[antotom]

Thanks a lot for your reply.

That can work for ID Code and Company Name, but not for the YYYYMM column.

Every time we retrieve this file there is a new column at the end to take into account the last month just passed of the current year. So if I retrieve it today, my last column will be 202210 (october 2022), but if I do it in 1 month it will be 202211 (november 2022).

How can I keep this into account?