Remove columns using powerquery

[taylo40]

Hello,
I am new to PowerQuery in Excel and have the following question

I receive a spreadsheet every month and it contains around 40 columns of data
Within these columns, there are 12 which represent the months of the year
within the header, it will specify whether the value is a forecast or an actual.
For months prior to the current month, it is likely to say actual
For months greater than the current month, it is likely to say forecast. Example :
January 2017 Actual
February 2017 Actual
March 2017 Actual
Aril 2017 Forecast
May 2017 Forecast

Each month, there will be more actuals and less forecast.

My question is, in Power query, can you automatically remove columns should the heading say actual ?

Regards,

Jamie

 

[Andrzejek]

Can you ask whoever provides you with the data to give you just the 'Actual' data and skip the 'Forecast'?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[taylo40]

Hi,
Thanks for the comment. This .xls is from a 3rd party supplier that shows all of the projects they are doing. The .xls is used for multiple different reasons so do not want to change the format. The reason I am not interested in the actuals is because my Finance department will also use the .xls and post additional accruals and therefore, more accurate actuals come from the Finance system

 

[combo]

You can't embed power query in xls file, however you can link your file in xlsx file with power query application. Moreover, you can make file name (and path) a parameter to adjust to changed file name in next run.

Power query is a powerful tool, you can do a lot of transformations with your data. I don't know how your table looks like, but assuming that you have Jan-Dec columns with data, actual/forecast depend on report date and the file is stored on the disk, I would create a new workbook (xlsx) and:
1. link to the data in the source (power query),
2. create a complete list of headers with monthly data, that have to be left or removed, with numbers 1-12 in separate column (excel, query it in power query),
3. create a one data row table with number of last month with actual data (excel, query it in power query),
4. convert (3) to function (power query),
5. create a list of months to remove, basing on (2) (3) and (4) (power query, add month (4) as new column, deduct from month number (3) in another column, filter with >0, remove helper columns, convert to list. I named it [tt]"qMonthsToDel"[/tt]),
6. remove columns in new query linked to (1) basing on the list (5) (power query, [tt]Table.RemoveColumns(Source,qMonthsToDel)[/tt], where [tt]Source[/tt] is a link to data (1).

Having this you can enter in excel (3) the number of last month with actuals and refresh the query.

I don't know your experience with power query, it's specific not very complicated, one should know the basic ideas (working with tables, adding queries to tables just to to represent data, some relations between basic and advqnced editor view, some M ideas and naming convention), otherwise it's easy to get error and stop without any complete help.




combo