I need to automate a monthly report currently done manually.

[Nial]

Hi,

I am a beginner using power query so will not be comfortable using M.

I need to compare the latest report say October, with September's, next month November with October and so on. It needs to flag status changes in records as well as new records. These reports are not financial.

Currently it is done manually in Power Query, I want to be able to set up a master report that can just be refreshed monthly. The source reports are held in a folder and and have the month name in the title. I cannot see how to get power query to run automatically if the source files have different names each month. Any help or pointers would be appreciated.

Thanks in advance

Nial

[Nathaniel_C]

Hi @Nial ,

You are welcome! Excel and I go way back, now I am working as a Data Analyst with Power BI.  Power Query is now in the background for both of them. I spent decades cleaning up data in Excel.  When I read through the book and did the hands on exercises, I kept being amazed that geniuses behind the software kept supplying me with solutions that I previously had done by hand.  The authors of the book make it very easy and very quickly you can be an expert with the user interface.

Cheers,

Nathaniel

[Nial]

Thanks Nathaniel. Very helpful. I think I now understand how to proceed I think? I could filter the list of source reports to get Power Query to just run on the the latest 2 reports in the folder. But I am still struggling on how to extract the differences beween the 2 reports once combined, there is no handy date column. For the manual process I loaded the 2 seperate reports and used merge (Right anti) to capture rows only in the newsest report, run against record id and status. I do not know how to extract this data from the combined report loade via the 'folder' route?

I will check out @KenPuls book.

Appreciate your help.

Nial

[Nathaniel_C · Accepted solution]

Hi @Nial , To automate the selection on the last two months, you could either set up parameters, or filter for rows based on the created date, or the modified date depending on your business process.  Pictures below. The UI is so amazing for Power Query and so many things you can do.


If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nial]

Thanks @Nathaniel_C, I will use your helpful advice and see if I can create the ideal solution to the problem.

As you say it the UI is amazing, and an to a non-coder like myself has the potential to solve many Excel processes where formulas and code would have had to been used previously.

Thanks for the link, I can see this site being my goto place for increasing my Power Query knowledge I am yet to dip my toe in Power BI but envisage sometime in the future I will.

All the best

Nial

[KenPuls]

"If you go through all the exercises, you will never have to say that you are not conversant in Power Query. Right @KenPuls ?"

Absolutely @Nathaniel_C! 😄

[Nathaniel_C]

Hi @Nial 
Get data, choose folder, browse to your location, select combine, it will bring the data - use the first file as an example.  Then if you wish you might pivot the column so that you can compare by the month.

Sorry the pictures might not be in order

Let me highly recommend this book which you can get on Amazon for around $30. If you go through all the exercises, you will never have to say that you are not conversant in Power Query. Right @KenPuls ?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel