Excel formula sheet to power bi?

[Amygonz]

I have a multi-sheet workbook of excel with many formulas. How do I get into power bi and refresh monthly or weekly?

[Anonymous · Accepted solution]

Hi @Amygonz ,

Please try below steps:

1. Import the excel data in to Power BI Desktop with the built-in excel connector

2. create data model and visualize data

3. save as a .pbix file, and publish it to Power BI Service

4. because your data is hosted in local machine, you need install and configure On-premises data gateway

5. when you have finished configure gateway, you could set the schedule refresh policy in the Power BI Service Portal, Power BI support daily and weekly refresh interval option, you could use Power Automate to create a custom refresh interval that occurs monthly, as described in the following Power BI blog post. 

For more details, you can read related document: Configure scheduled refresh - Power BI | Microsoft Learn , Install an on-premises data gateway | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Amygonz]

Do I have to create a data model first in excel? It seems I can create in excel for faster turn around for refreshing monthly and then upload to bi service and to link to power bi service for visuals. Or does dataset upload to bi service do the same thing?

[rubayatyasmin]

Hi, @Amygonz 

Publish the report to PBI service then schedule refresh. Remember to use a gateway. 

Refer: https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh

[RossEdwards]

My recommendation would be to have a network drive to store the file on.  Use Power BI to import the sheets from that network drive.  Be careful not to reference the drive using a Drive letter (like K:\Folder\File.xlsx) but via its full name (like server\\Folder\File.xlsx).

Once you have made your Power BI report, publish it to the Power BI service.

Once on the service you will need an On-Premises data gateway.  This will allow your cloud Power BI report to access your internal network.

With a gateway set up, you need to make a connection to the file.

Apply the connection to your power bi report in the service in the dataset settings.  I find it easy to click on the "Schedule refresh" button to get here.

Once you have applied the gateway, set up the schedule and you can choose a weekly refresh and select what time of day and day of the week it will run.