Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dooriya101
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. 

Picture1.pngPicture2.pngPicture3.png

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?

 

 

 

1 REPLY 1
v-jingzhang
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. 

vjingzhang_0-1661838976295.png

vjingzhang_2-1661839911348.png

 

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. 

vjingzhang_3-1661841002505.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors