Manually maintained datasets

[msprog]

In PowerBI development, how can you manage manually maintained datasets like mapping excels. we are using PowerBI, most of the data comes from the databases. However, we have some manually maintained mapping files which are mainly excels, that need to be managed. Some of these excels are static , whereas some are updated once a week.

What are the approaches to managing and ingestion of these files?

Please suggest

thanks

[Poojara_D12]

In Power BI development, managing manually maintained datasets such as mapping Excel files—especially when some are static while others are updated periodically—requires a thoughtful approach to ensure consistency, accuracy, and ease of maintenance. One common method is to store these Excel files in a centralized and secure location such as SharePoint Online, OneDrive for Business, or a shared network drive that Power BI can access. For files that are updated regularly, storing them in SharePoint or OneDrive allows seamless refresh through scheduled data refreshes in the Power BI Service. You can use Power BI’s web connector to link to these sources, which keeps the connection dynamic and ensures that the latest version is used at each refresh. For static files that do not change, you can either embed them in the PBIX or keep them in a version-controlled shared location to ensure transparency. It’s important to maintain a clear naming convention and folder structure to distinguish between static and dynamic files, and ideally document the purpose and update frequency of each file within the dataset or project documentation. Additionally, using parameters or separate queries to handle different environments (such as dev, test, and prod) can help streamline deployment and maintenance. If the Excel files are critical to business logic, consider eventually migrating them to a controlled database table to reduce dependency on manual files and improve governance.

[rohit1991]

Hi @msprog 

For manually maintained datasets like Excel mapping files, the best approach is to store them in SharePoint Online or OneDrive for Business. Power BI integrates well with both and can auto-refresh from these sources typically every hour via OneDrive refresh.

If the files are updated often, consider using dataflows to centralize and reuse the logic across reports. For static files, a one-time import might be enough.

If you're using on-prem files, you'll need a gateway and a scheduled refresh. Also, standardizing file names and using SharePoint version history helps with governance and traceability.

[msprog]

Thanks @rohit1991  this is very useful. 

One observation from my test though: 

I have set OneDrive refresh to ON and the Sync with OneDrive and SharePoint to Automatic.  even then i find, that unless i open the PBIX(stored on onedrive) and hit Refresh, It doesn't sync to the PBI service. 

If i just update the excel (stored on onedrive) - excel is the source of data - it doesn't auto sync. the moment i open the pbix in the desktop and click refresh and close, i notice it syncs,

Form what you described, if i jsut update the source excel, the PBI service should auto sync - this is not happening for me, I am not sure what i am doing wrong, 

thanks

[Akash_Varuna]

Hi @msprog  Store Excel files in SharePoint or OneDrive for automatic syncing with Power BI. Use Dataflows to preprocess and centralize transformations for reusable mappings. Set up scheduled refresh for updated files and ensure gateways are configured for on-premises files.

If this post helped please do give a kudos and accept this as a solution
Thanks In Advance

[msprog]

@Akash_Varuna  thanks. please are you able to explain this: Store Excel files in SharePoint or OneDrive for automatic syncing with Power BI. 

Also, I am a confused between these 2 settings for PowerBI semantic model :

OneDrive refresh and Sync with OneDrive and SharePoint

Is there a inter-relation between these 2 settings, What is the difference between these 2 settings? 

[v-achippa · Accepted solution]

Hi @msprog,

Thank you for reaching out to Microsoft Fabric Community.

“Store Excel files in SharePoint or OneDrive for automatic syncing with Power BI”

This means instead of keeping your manually maintained excel files on your local machine or shared drives, you upload them to SharePoint Online or OneDrive for Business.

Once stored there, power bi can directly connect to the file stored in the cloud. If the file is updated (every week) power bi can automatically pick up the changes during refresh. There is no need to manually upload or re import the file every time something changes.

Differences:

OneDrive Refresh:

This is automatic synchronization that occurs approximately every hour. Power bi checks the file stored in OneDrive or SharePoint Online and syncs any data changes to the semantic model. This works only when the file is imported from OneDrive/SharePoint using the Get Data --> OneDrive or SharePoint options in power bi Service (not local paths).

Scheduled Refresh:

This is a refresh of the data inside the power bi dataset. We can schedule it (daily or weekly) to pull the latest data from excel files, databases or other sources. Even if OneDrive refresh is active, still need scheduled refresh to ensure the data inside the report is up to date.

So, to manage your weekly updated mapping excel files, store them in OneDrive or SharePoint Online, connect using Web connector or SharePoint connector and set up a Scheduled Refresh in power bi service.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @msprog,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @msprog,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa