Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Python calculation in Power Query

Hi All, I'm quite new to Power BI and Python and seeking your advice here.


1. My raw data is from company website, usually I need to manual export some certian excel files and apply to different power query.

Q1: is that possible to use Python to auto catch such data? The website itself not showing the data but just some charts and reports, the excel must be download manually, in my understnading it could be realized by RPA but just want to know if it's possible by python?


2. my raw data records company Incident details, and in Power Query, I want to calculate the total case number., but the raw data has some other way to record so the count of rows or the count of distinct Incident Number cannot reflect the true total number of it, the only way I can see is like: if any two incidents, their Group, Time, & Incident Number are the same, then it can be count as 1 case. So I try to choose these three columns first, but it shows below error. 


Q2: is the code correct to meet what I'm going to do?

Q3: what does this error msg mean and how can I remove it? 

Q4 could you give some hint like how can I do the python code with pandas? I'm bit lost how to realize what I want........


These may be some stupid question but seeking your help or guidance, appreciates a lot!




But somehow in Power Query Python script, I see lots examples use pandas but I've no idea how to use this to run..... Could you help to give some hints?




Community Support
Community Support

Hi @Dooriya101


Without knowing how the website looks like, it's difficult to tell whether it's possible to download the file by Python. Since you are using Power Query, sometimes it's possible to get data from an online Excel file by the Web connector in Power Query Editor. 


For example on this website, when hovering over a "Download" button, you can find a link in bottom left corner of web browser. This is the link of the file. We can copy this link and paste it to URL textbox in the "From Web" window of Web connector to connect to this file and get data from it. In this scenario, it is possible to use Python to download the file too. 




For the rest questions, I recommend to use Power Query to calculate the number of total cases. There is a Group By feature that can deal with the problem simply. You can group by Group, Time and Incident Number at the same time. After that, you can count rows of the table to get the total case number. 



Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors