Power Query spits out incorrect values in excel

[mahajanudit2005]

I have an excel report that has several tabs and gets automatically refresh through a python script. One of the excel tabs underlying query in Power Query Editor ( basically SQL statements) has a column called Ranking. When I open power query editor, the column contains correct values, however, the excel itself shows incorrect values in that column, instead of proper rank ( its basically a windows function in sql statement) , it shows indexed values ( ie instead of ranking, it shows 1,2,3,4..etc). Now, If I manually refresh the excel report, the values surprisingly gets corrected. Not sure why it is happening. 

[Anonymous]

Hi @mahajanudit2005 ,

Thanks for jennratten's reply!
And @mahajanudit2005 , Does this problem occur every time you run the python script to automatically refresh? 
The possible reason I can think of is that the Python script might be refreshing the data before the Power Query Editor has finished processing. Make sure the script waits until all Power Query operations are completed. Check if the ranking column depends on other queries or steps. If so, make sure all queries are refreshed in the correct order.
Alternatively, you can try adding a step in your Python script to log the refresh status or any errors encountered to ensure that the script is executing as expected. Alternatively, manually step through the script to see if any errors or warnings pop up during execution.
If the problem persists, then as jennratten said, we may need to look at the SQL code and M queries you are using. Thanks!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jennratten]

Hello @mahajanudit2005 - this could be for a few different reasons, depending on the actual query script you are using.  Can you please post the script here?  If the rank is being calculated using Power Query (M) code then what you see in the query preview is just a preview, and should be indexed prior to loading to the model.  If you are calculating the rank using some other method then we would need to see the scripts to better assist.  Can you please reply with a copy of your script and some screenshots?