Automating the same weekly report

[Anonymous]

Hi, I'm fairly new to power BI, have watched a few tutorials and tried to do this myself, but I'm left wondering is the software capable of my endgoal. 

I am attempting to automate our daily and weekly reports.

Our process is simple, but time consuming and there has to be a better solution.

We currently extract the data from our database into an excel file. However, the data is poorly formatted and messy in this spreadsheet, and we spend a few hours a week just editing the excel file and making it presentable.

I'm wondering if doing this process once, manually, in Power BI allows me to do it automatically from then on?

As far as I can tell, and from the little knowledge I have, Power BI records all the steps you take whilst editing your data.

My question is, can you save these steps and apply them automatically to future data in the same format?

[RichardJ · Accepted solution]

Hi @Anonymous,

Power BI could save you a lot of time by automating this process.

It would be preferable to connect to the source database directly though the excel export could still be used if necessary.

The data would then be pulled into Power BI and 'cleaned up' as part of the import process.

The import process, once defined is repeated by Power BI so saves the manual data manipulation currently required.

I've been really impressed with the ease of use and tools available to manipulate and tidy up data.

Cheers,

Richard

[mahoneypat · Accepted solution]

What you are describing is exactly the situation where Power BI shines.  Not sure if you are replacing your file(s) each time or adding to a folder.  Either way, Power BI can help you automate most if not all of your process.  This includes not just the data transformation/integration part with the query editor, but also data analysis with DAX, data visualization, and report distribution (if you publish your reports to the Power BI service).

If you get stuck with your messy files, post example/mock data on the community and we can show you how to transform it to an easily analyzeable state.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi Pat,

Thank you very much for your help.

I've figured out how to apply the same edits for each report with the advanced editor and it's a quicker process now.

My next step is hopefully to automate this. Currently, I have to paste in my code to the advanced editor to make the changes. I'm wondering if you know of a way this can be done automatically? 

Any help would be greatly appreciated.

Thanks, Jerry

[RichardJ · Accepted solution]

Hi @Anonymous,

Power BI could save you a lot of time by automating this process.

It would be preferable to connect to the source database directly though the excel export could still be used if necessary.

The data would then be pulled into Power BI and 'cleaned up' as part of the import process.

The import process, once defined is repeated by Power BI so saves the manual data manipulation currently required.

I've been really impressed with the ease of use and tools available to manipulate and tidy up data.

Cheers,

Richard