Kumail's avatar
Kumail
Impactful Individual
7 years ago
Status:
New

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

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

Reports

5 Comments

Comments have been turned off for this idea
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    7 years ago

    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 

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    7 years ago

    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's avatar
    HadiRajani
    Frequent Visitor
    7 years ago

    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's avatar
    Kumail
    Impactful Individual
    7 years ago

    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

Completed Ideas