Refreshing the Power BI report based on an excel file with dynamic column structure

[Anonymous]

Hi,

I have a question how I could optimize the currently existing process of refreshing the Power BI app (built on a report), that I created. 

The thing is that it's based on an excel file, published through a corporate sharepoint and this excel file each month gets data for a consecutive month, which is an additional column on top of the previously existing ones. Becasue adding a column for Power BI understandably means changing the structure of a file, despite the file being on a sharepoint (run by Microsoft platform) the refresh doesn't happen automatically. Consequently, what I am doing currently each month is opening the Power BI desktop, clicking refresh, publishing to Power BI services and then refreshing an app, that is available to  users. This however seems to be a bit manual for the modern platform such as Power BI. Is there a way to automate these refresh steps ? 

Regards

Krzysztof

[Anonymous]

Hi @Anonymous ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Stephen Tao

[Anonymous]

Hi @Anonymous ,

In Power BI service, you can set scheduled refresh to achieve automatic refresh, please kindly refer to: Configure scheduled refresh.

A gateway is required for scheduled refresh, please download the gateway.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Anonymous ,

To what I can understand the information you add to your file is the additional month correct?

What I did in a previous project was to unpivot the month columns, this was done by unpivot other columns. Explaining a little bit better lets assume a structure like this:

Cat	Cat	Month1	Month2	Month3	Month4
1	1	1	1	1	1
2	2	2	2	2	2

Now select columns that are not months and unpivot other columns

This should allow for the refresh without need of manual intervention.

If you have a step of promote headers, this needs to be done before that step.