This summer, we kicked off a surge of hiring on the Power BI team. With so many different roles across disciplines, levels, hiring managers and countries, there was no central place for interested candidates to see what was available. In order to solve this, I created a Power BI report that scrapes the Microsoft careers site for all Power BI roles and hosted them in one handy Power BI report. Using Publish to Web, I was able to share this both internally with Microsoft employees and publicly with external candidates.

Lots of folks asked me how I built this and if I could build one for their team. This post will cover how I built it.

Power BI is still hiring! You can find this report at aka.ms/pbiroles.


Step 1: Add a unique search term to all your open roles.

We added #PowerBIJobs to all of our open positions across the org.  This will give you a page on the careers site that is ready to be scraped by the web connector in Power BI Desktop. Once you have that search term, you can search the careers site and grab the URL. In our case, it was https://careers.microsoft.com/us/en/search-results?keywords=%23PowerBIJobs.


Step 2: Use the web connector to connect to your job listings.

For anyone getting started with Power BI, I always recommend the web connector as a way to get started. It offers a simple way to scrape any content off of a website and pull it into Power BI.

This is where you include the URL you pulled from the careers site in the last step.


Step 3: Add a table using examples

Sometimes, Power BI will automatically detect the right data from the format of the HTML on the webpage but often times the data isn’t that well organized. This is where Power BI makes it really easy (and dare I say, fun?). Instead of selecting one of the tables Power BI detected, click the “add table using examples” button in the lower left hand corner.

Now, you just start typing examples of content from the webpage that you’d like pulled into your model. If you give a couple examples of the job titles, Power BI will detect what you’re trying to do and fill in the rest automatically. In the November release of Power BI Desktop, we added the ability to extract web URLs from websites as well. This will let you extract the job listing link into your model. Once you select all the columns you want to extract, click ok and select the custom table you just generated to load into your model.


Step 4: Build your report

From there, you can use the data you just pulled in from the careers site in Power BI Desktop. I added a couple slicers and word cloud to help people search through all our roles for something that might be relevant to them.

Once I published it to the service and set up refresh on the dataset, I used publish to web to grab a URL that I could share publicly. I created a shortened URL aka.ms/pbiroles to make it easier for folks on the team to distribute and put in their email signatures.


A shortcut if you’re using the Microsoft Careers site

If you are using the Microsoft Careers site as your starting point, here’s my M code that the web connector generated for me. You can copy this (changing out the URL for one for your team) and paste it into Power BI, skipping all the steps above. To do this, go to Get Data > Blank Query and paste in the following code:

let
Source = Web.BrowserContents("https://careers.microsoft.com/us/en/search-results?keywords=%23powerbijobs", [WaitFor=[Timeout=#duration(0, 0, 0, 2)]]),
#"Extracted Table From Html" = Html.Table(Source, {{"Role", "[data-ph-at-job-education-level-text=""""]"}, {"Location", ".job-location"}, {"Discipline", ".job-category"}, {"Date Posted", ".job-date"}, {"Description", ".description"}, {"URL", "a[href^=""http""]",
each [Attributes][href]}}, [RowSelector="[data-ph-at-job-education-level-text=""""]"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Role", type text}, {"Location", type text}, {"Discipline", type text}, {"Date Posted", type date}, {"Description", type text}, {"URL", type text}})
in
#"Changed Type"

Categories: Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *