Update in Excel File using Python library OpenPyXl and refreshing data in Power BI BUG

[Kumail]

Scenario:

This "Example.xlsx" file data is queried in MS Power BI. When Power BI is refreshed, system gets latest data from excel into Power BI. The issue is, when we get the excel data updated using the OpenPyXl Python library and refresh power BI, the data doesn't get updated in Power BI. However, when we manually update and save the excel file, data gets updated in Power BI on refresh. In both the cases, data in excel file is correctly being saved.

Steps:

1.Open example.pbix and refresh. (The data gets refreshed.)

2.Run Python Code. (It will update example.xlsx file.)

3.Again, refresh example.pbix file, it wouldn’t get refreshed and will show blank values, however, excel file has been updated. 4.Now open example.xlsx file, press ctrl + S and close the file.

5.Again, refresh example.pbix file. It will get updated.

In other words, When we update and save data in Excel using OpenPyXl Python library, some calculations are performed on it in excel, and refresh its powerbi connection, it shows blank table cells. When we again manually save the excel file and close it, the MSPowerBI connection works (It gets the calculated data from excel).

You can find files from the url below.

https://github.com/KumailQazi/PythonExcelPowerBIRefresh

[v-qiuyu-msft]

Hi @Kumail,

I'm not able to reproduce the issue with Power BI desktop version 2.62.5222.761 64-bit (September 2018). Please update to the latest Power BI desktop then test again. If issue persists, please create a support ticket to get dedicated support. 

Best Regards,
Qiuyun Yu 

[Kumail]

Hi @v-qiuyu-msft

Thanks for reaching out.

When create suppport ticket is clicked, system redirects to https://powerbi.microsoft.com/en-us/support/free/ . Here system asks for sign in and when signed in, it redirects to power bi service account. 

Could you send images of the file of your test?

I tried again, however, the result is same.

[v-qiuyu-msft]

Hi @Kumail,

I modify one value in your .py file and run it, then click Refresh button, the updated data will show in Power BI desktop. You can see my Excel file, .py file and pbix file here. 

Follow the steps in gif image shared in my previous post, it will redirect to this page: https://powerbi.microsoft.com/en-us/support/pro/ticket/

Please try again on your side. If you still are not able to open a ticket, please contact you IT admin. 

Best Regards,
Qiuyun Yu 

[HadiRajani]

@v-qiuyu-msft I think @Kumail is right. The process follows with the code, when the code is run, it updates excel file, in the excel file the column B contains the value send by the code, and column D and F contains calculated values from column B, such as D2=B+2. When the code is run and it updates excel file we have created a .pbix file and this Example.xlsx is the data source of the .pbix file, when we refresh the .pbix file, it only updates column B values while column D and F are not updated, then we manually save excel file with ctrl+S and then refresh .pbix file so it updates the other two columns also. GIF is attached for further understanding

[Kumail]

Hi @v-qiuyu-msft

Thanks for sharing the image. In the image also, "column 2" is updated, however, "Salary" and "Column 6"  shows blank values even in your images, while in excel these are showing values.

Regarding ticket, it is redirecting to service account and then to https://powerbi.microsoft.com/en-us/support/free/ page.

Regards

Kumail