Power query with variable source data

[Excelnewie]

Hi all,
Hoping someone can help:

I am trying to set up a 'template' that will clean monthly spreadsheets.

I get a spreadsheet from an external source with almost 300 columns each month. I only need about 20 of these columns. Unfortunately, depending on who sends me the spreadsheet, the columns are not always in the same order, despite having the same names. 3 of these columns contain figures from the previous three months, hence these column names regularly change (month names).

I can create a query extracting columns based on name OR position, but not both.

Any suggestions on how I can do this?

Thanks in advance.

 

[pjmorris]

I would use Table.ColumnNames to obtain a list of the column names. You can then create a list of column names that you require - this can then be included as a parameter in: Table.ReorderColumns(table as table, columnOrder as list, optional missingField as nullable number) which will then give you a standardised table.

To manipulate the list of column names use the List functions.

Not a great answer, but it should provide a route to your solution.

Regards