Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Nial
Regular Visitor

I need to automate a monthly report currently done manually.

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

1 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.param2.PNGparam.PNG


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nial
Regular Visitor

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

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.param2.PNGparam.PNG


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Helper II
Helper II

"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
Super User
Super User

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


Folder2.PNGFolder1.PNG

Folder6.PNGFolder5.PNGFolder4.PNG

 

Nathaniel_C_0-1637373426707.jpeg

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors