Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, We have created a Power BI dashboard from an MS Excel file using BI Desktop and published on a workspace on PowerBi web app. Dataset also published with that. Now we want to use auto-refresh using one drive. Do we need to created all dashboard again on workspace using IE. Or we can connect Onedrive file to existing published dashboard?
Solved! Go to Solution.
Hi @asifnazir,
From your description, it seems you create a report get data from local Excel. Now you want to change the local Excel to OneDrive Excel file for the report, right?
In your scenario, you can open Query Editor in Power BI desktop, get data from OneDrive for Business Excel file follow this article. If it's OneDrive personal, follow v-yuezhe-msft's suggestion here. After import data, make sure the table structure is the same as the one you get from local Excel. Then delete the original table from local Excel, rename this new table with the same name as deleted one, apply changes to load to data model. Please note, if you have created calculated columns or measures in the original table, those calculated columns or measures will lost and you need to recreate them. Before perform this replace action, please backup original report so you can create calculated columns or measures follow original report.
By the way, you can also get auto refresh for the original report though get data from local Excel. As the data source is on-premise, you can install on-premise data gateway on the machine which has Excel stores. Then go to the Manage Gateways, create a File data source point to the local Excel file use the same path as you defined in Power BI desktop. Then you can go to Dataset Settings, configure this dataset to use this data gateway and set schedule refresh.
Best Regards,
Qiuyun Yu
Hi @asifnazir,
From your description, it seems you create a report get data from local Excel. Now you want to change the local Excel to OneDrive Excel file for the report, right?
In your scenario, you can open Query Editor in Power BI desktop, get data from OneDrive for Business Excel file follow this article. If it's OneDrive personal, follow v-yuezhe-msft's suggestion here. After import data, make sure the table structure is the same as the one you get from local Excel. Then delete the original table from local Excel, rename this new table with the same name as deleted one, apply changes to load to data model. Please note, if you have created calculated columns or measures in the original table, those calculated columns or measures will lost and you need to recreate them. Before perform this replace action, please backup original report so you can create calculated columns or measures follow original report.
By the way, you can also get auto refresh for the original report though get data from local Excel. As the data source is on-premise, you can install on-premise data gateway on the machine which has Excel stores. Then go to the Manage Gateways, create a File data source point to the local Excel file use the same path as you defined in Power BI desktop. Then you can go to Dataset Settings, configure this dataset to use this data gateway and set schedule refresh.
Best Regards,
Qiuyun Yu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.