Best way for me to automate reports

[MrEbzz]

Hi, Looking to find the most efficient way to automate report cleaning.

I currently have a macro in place via excel that does this for me, however i have heard many good things about powerBI and powerQuery.

The steps from when the report arrives to when its sent off cleaned and accurate never change. Only the data that's being cleaned changes.

For example.

Delete all rows containing 123 in column A
Delete all rows containing test in column A
Change all data in column X to FALSE where = 0 and TRUE where = 1
Filter data dependent on Column D (status)
Allocate filtered data (ROWs) to newly created work book, with the corresponding tabs (sheets) for each Status. i.e. Status New = New_workbook > sheet 1 (Holds all rows containing status Closed)), Status Closed = Same "New_workbook" (that's already been created) sheet 2 (named: Closed, Holds all rows containing status Closed)


Can anyone think of a better method then a macro? - if this helps the raw report comes in via excel document.

 

[Matt Allington]

Power Query can easily execute these steps, but it’s not the same approach as VBA. In doing so, it will create a new *table* of data in the same workbook as PQ. So either PQ has to be in the source workbook (I assume that won’t work given it seems you get sent a new one each day) or it needs to be in the destination workbook, the one that will contain the cleansed table of data (and PQ). Each time you refresh the PQ against the new data, the table will change. You can of course save with a new name if needed. If that’s not what you want, then VBA is the way to go