Forum Discussion

msprog's avatar
msprog
Advocate 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

rohit1991's avatar
rohit1991
Super User
1 year ago

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's avatar
    msprog
    Advocate III
    1 year ago

    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