Data Storage

[Peter_Yaacoub]

Hi everyone!

We are new to Power BI and would like to visualise our results with the option of a monthly update based on the new data fed in.

The issue we are facing is the data being stored in human-friendly excel files (which are later converted to pdf reports) in an unstructured manner.

I am looking for your tips/advice on how to proceed with organising/storing my data to meet my end-goal of automatic monthly updates.

Thank you in advance

Peter

[serpiva64 · Accepted solution]

Hi, 

if i understood your problem, you need to create an excel file for each month and store them in a folder. In this folder each month you add a new file.

Then instead of loading the single file you load the folder.

[Peter_Yaacoub]

Hi Serpiva,

Thanks for your response. Above is a screenshot of a table I am preparing from the unstructured excel files. I understand it needs to be in flat format before being loaded to Power Bi. I am not sure how to take it from here so monthly data is added resulting in a dashboard update.

[serpiva64 · Accepted solution]

Hi, 

if i understood your problem, you need to create an excel file for each month and store them in a folder. In this folder each month you add a new file.

Then instead of loading the single file you load the folder.

[Peter_Yaacoub]

Hi Serpiva,

Thanks a lot for the solution I will definitely try this. I was wondering if it's possible to create one table and adding the new data to it through the "Append Queries". 

[Anonymous · Accepted solution]

PowerQuery would automatically "APPEND" data from all the Monthly Files to the same table. It becomes easier if you have a name pattern for the files like "SalesDate_YYYYMM.xlsx". 

[Anonymous]

You have various options with PowerBI for the updates (based on the license you have - Pro or Premium).

You could use Data Alerts or your users can use Monthly Subscription to the report or you can use APIs to push the Reports to your users.

[serpiva64]

Hi, if you can provide some sample data it would be easier to evaluete if there is the necessity to modify excel files and in which direction