How to refresh a report when excel is used as source?

[Anonymous]

I am new to POWER BI and require some help in refershing the data. I recently created a dashboard using excel as source for the data. The dashboard works fine, however, if I have to refresh the data in excel file what should be the steps that I need to follow. 

Basically, I have following questions?

1. How do I refresh the data so that it reflects in dashboard, especially when excel is the source file?

2. If am able to refresh the data will the changes be reflected in the all places? I mean even if I have shared that dashboard will recepients also see the changes that was made?

Thanks!

[SaritaSenapati]

Hi @Anonymous,

What method are you using to connect to onedrive? if you are connecting to your local one drive folder then you will need a personal gateway for detecting changes.

But if you do not want perosnal gateway and still want to be able to access one drive excel workbook and the changes, then follow the below steps ( This method works for me):-

1) open onedrive in your browser.

2) right click at your excel. you will see two options; open in excel and open excel online.

3) Select open in excel. once your fiemis open in excel , select file tab.

4) Under Today, you will see your file name. Right click and select copy path to clipboard.

5)Now open you power BI desktop and click on get data and select web.

6) In url, paste the path you copied in step 4.

7) When prompted for sign in, enter the credentials you use for one drive.

😎 Click connect. When connected , it will show you the files you want to import.

9) After you import, Go back to onedrive and make changes in your file. ( delete columns, or add columns or add new rows)

10) in power Bi desktop home tab, click on refresh. Power BI desktop will show you your latest dataset.

There is also a wonderful step by step guide with pictures which you can use http://exceleratorbi.com.au/data-modelling-onedrive-source-files/

I hope this information helps!

regards,

Sarita

[v-ljerr-msft]

Hi @Anonymous,

---

Basically, I have following questions?

1. How do I refresh the data so that it reflects in dashboard, especially when excel is the source file?

---

You can configure a schedule refresh for your dataset. For more details, you can refer to this article.

---

2. If am able to refresh the data will the changes be reflected in the all places? I mean even if I have shared that dashboard will recepients also see the changes that was made?

---

Yes, the recipients can see the changes you make. See notes about sharing a dashboard.

Regards

[Anonymous]

Hi PKR

There are two solutions:

If you allreaday own OneDrive, then you may be fine with the suggested solution.

If not, then consider to import the data from Excel into Power BI Desktop and to publish your report to Power BI Service.

[annamalaig]

Pfister - Could you please confirm how to import the data instead of keeping the active connection? In the data source settings, i have selected "Import" instead of direct query.

[SaritaSenapati]

Hi,

It would be a good idea to have your excel sheets in onedrive as power bi will be able to detect the changes you make in your workbook( Changes can be adding new columns, adding new data, changing existing column/data, adding/ changing measures )

For more details you can check the below link:

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-excel-file-local-drive/

[Anonymous]

Hi,

Thanks for your response.

I tried to keep an excel file in Onedrive and tried to connect through POWER BI destop. Initially everything worked smoothly without any trouble (although it took longer to connect), however, when I tried to make changes (I deleted few columns of table) to file in Onedrive and refreshed the data from POWER BI, I got an error stating that "Expression Error" stating that a particular column was not found by Power BI. 

Is this expected? The same is found even when header name is changed. 

I am having a data in an excel and I was able to successfully create a dashboard using that data, but this information is subject to frequent changes, so I am looking for ways to refresh that data (column headers, addition/deletion/reorder of columns) in Power BI without any problem. Currently, I am recreating the entire report/dashboard if I make even a small change to the data model.

Please guide me if I am missing any key points.

[SaritaSenapati · Accepted solution]

Hi @Anonymous

For change header types – well, if you go to query editor, you will see the column names are hardcoded in the formula bar. Hence, when you change the column header names, power Bi will throw an expression error, as it is not able to find the column present in the formula bar.

To fix this issue, in query editor window, delete changed type under applied steps. Now when you change column header names, the new name will be reflected in power Bi as well. Same behaviour applies for deleted columns.

Let me know if this helps or if you found a different solution.

Regards,

Sarita

[Anonymous]

Hi Sarita,

Thanks for your suggestion!!

After I removed the "Changed Type" under "Applied Steps" all my changes (addition/deletion/modification of column) is reflecting when I refresh the data. 

Regards,

PKR

[Pandreoni]

Thanks for this. Mysteriously it does happen that sometimes my query is refreshed correctly, sometimes not. I tried to use that step, looks like its working.

Just a question: if I remove Changed Type and I close / reopen the Query Editor, Changed Type is still in the applied steps. Is that normal? i clicked Apply & Close.