Forum Discussion

msprog's avatar
msprog
Icon for Advocate III rankAdvocate III
1 year ago
Solved

Manually maintained datasets

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...
Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    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

Poojara_D12's avatar
Poojara_D12
Icon for Super User rankSuper User
1 year ago

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.