As a Power BI developer I know how Power Query makes it easy to get data from web sources, but I also know there are limits to what it can do. I can get data from tables on web pages, I can get data from web services, but when confronted with a website where you have to click a button to download a file there’s a problem. Take https://data.police.uk/data/ for example:
This site allows you to download crime data for England and Wales, but to do so you need to check some boxes and then click the “Generate file” button at the bottom (yes I know there’s also an API, but this is only an example). You can’t click a button in Power Query.
Enter Power Automate UI Flows. I’ve been a fan of Power Automate for a while now but it was only the release of the new Power Automate Desktop preview last week that I decided to check out its Robotic Process Automation (RPA) capabilities. Basically, if you’re still doing lots of manual downloading, clicking, moving files etc before you can load your data into Power BI then Power Automate UI Flows can do all this for you automatically by recording and replaying everything you do.
Now I’m not a UI Flows expert by any stretch of the imagination, but within an hour I was able to create a UI Flow that:
- Opened a browser and went to https://data.police.uk/data/ (quick tip: if you want to save a file from a browser you need to use the special Automation Browser, not Chrome, Edge or Firefox)
- Checked the boxes to download crime, outcome and stop and search data for Thames Valley Police
- Clicked the Generate file button
- Clicked the Download Now button on the next screen when it appeared
- Downloaded the resulting zip file to a particular location
- Unzipped the file to a subfolder
- Deleted the zip file
Here’s what it looked like in Power Automate Desktop:
Now this is my first ever UI Flow so it’s probably not as robust as it could be, but it seemed to work ok. I was then able to call it from Power Automate and refresh a dataset once the UI Flow had finished:
I guess the point of this post is not to tell you how to use Power Automate UI Flows to extract data from web pages for use with Power BI, just that it’s there for you to use if you need it. It’s important to be aware of the other tools that are available in the Power Platform and understand how they can be used alongside Power BI, rather than focusing on trying to solve your problems with Power BI alone. Not only are there a lot of cool things happening with Power Automate that are directly relevant to Power BI (such as the new Export actions and the upcoming ability to kick off a Flow when a Power BI dataflow finishes refreshing – see the demo at 33:00 here) but there are other features in Power Automate like UI Flows that can be used for BI purposes, such as the ability to run an Office Script from a Flow which can be useful for preparing Excel data for Power BI.
If you want to learn more about Power Automate Desktop there’s a good multi-part tutorial on YouTube starting here; there’s also a three-part series on extracting data from web pages too (part 1, part 2, part 3) although it uses an example that Power Query handles much more elegantly 😉
I have this problem, and this post will help me a lot, thanks.
But, also, i’d like the power bi to insert my password in a site, and scrap a page, like i can fo with R and python, Is it possible?
Thank you very much.
The links on the article you linked appear to lead to an error. Do you have a direct link to download the desktop preview?
There also appears to be a requirement for a CDS Subscription, which is not setup automatically. For new users this may be confusing to do, and may incur additional costs?
Yes, I also found this quite confusing. To even get to this functionality seems to require new licences, creation of a new environment, CDS database etc. It’s not straight forward. It will go on the To Do List.
I tried to follow the example given in the article , but met with error when launching Automation Browser. I can only launch using Firefox/Chrome or Edge, but then I couldn’t click link to download file in those browsers. Not sure if anyone else encountered the same issue.