Beyond Automation: Visualizing Job Opportunities in Power BI

In my last blog post, "CareerBeacon Meets Python: My Enhanced Job Search Solution", I shared how I am using python to scrape job posting information from the web for employers I am interested in. However, the project didn't stop there. In this iteration, I focused on bringing all the data into Microsoft Power BI to create a more powerful and user-friendly job monitoring solution.

Bringing Data Into Power BI

If you know me, you know that I absolutely love working with Microsoft Power BI. It is such a versatile tool and I find the user experience in Power BI to be much smoother than dealing with tables/filters in Excel. Here’s how I approached it:

1. Data Consolidation: I used the "Get Data > From Folder" method in Power BI to pull in all daily job posting files from both GNB and CareerBeacon. This method allowed me to create two separate queries—one for each platform—within Power Query.

2. Custom Columns in Power Query: For each query, I used Power Query to enhance the data with additional custom columns. These columns allowed me to flag jobs based on specific keywords (e.g., roles related to data, process improvement, or leadership). I also ensured that the date from the file name was retained as a column, which was critical for filtering and sorting.

Here’s the Power Query code I used for the CareerBeacon dataset:

let
    Source = Folder.Files("C:\\Python\\CareerBeacon"),
    #"Filtered to CSV Files" = Table.SelectRows(Source, each [Extension] = ".csv"),
    #"Excluded Hidden Files" = Table.SelectRows(#"Filtered to CSV Files", each [Attributes]?[Hidden]? <> true),
    #"Transformed File Content" = Table.AddColumn(#"Excluded Hidden Files", "Transformed File", each #"Transform File (8)"([Content])),
    #"Renamed File Columns" = Table.RenameColumns(#"Transformed File Content", {"Name", "Source.Name"}),
    #"Selected Necessary Columns" = Table.SelectColumns(#"Renamed File Columns", {"Source.Name", "Transformed File"}),
    #"Expanded Transformed Table" = Table.ExpandTableColumn(#"Selected Necessary Columns", "Transformed File", Table.ColumnNames(#"Transform File (8)"(#"Sample File (8)"))),
    #"Cleaned JobBoardDate Name" = Table.ReplaceValue(#"Expanded Transformed Table", "CareerBeacon_", "", Replacer.ReplaceText, {"Source.Name"}),
    #"Removed CSV Extension" = Table.ReplaceValue(#"Cleaned JobBoardDate Name", ".csv", "", Replacer.ReplaceText, {"Source.Name"}),
    #"Converted Date Type" = Table.TransformColumnTypes(#"Removed CSV Extension", {{"Source.Name", type date}}),
    #"Standardized Column Names" = Table.RenameColumns(#"Converted Date Type", {{"Source.Name", "JobBoardDate"}, {"Job Title", "Job"}}),
    #"Added BI Flag" = Table.AddColumn(#"Standardized Column Names", "BI", each if Text.Contains([Job], " BI ") then "Y" else "N"),
    #"Added Analyst Flag" = Table.AddColumn(#"Added BI Flag", "Analyst", each if Text.Contains([Job], "Analyst") then "Y" else "N"),
    #"Added Leadership Flag" = Table.AddColumn(#"Added Analyst Flag", "Leadership", each if Text.Contains([Job], "Manager") or Text.Contains([Job], "Director") or Text.Contains([Job], "Lead") or Text.Contains([Job], "Vice President") then "Y" else "N"),
    #"Added Process Improvement Flag" = Table.AddColumn(#"Added Leadership Flag", "PIF", each if Text.Contains([Job], "Process") then "Y" else "N"),
    #"Added Risk Flag" = Table.AddColumn(#"Added Process Improvement Flag", "Risk", each if Text.Contains([Job], "Risk") then "Y" else "N"),
    #"Added Quality Flag" = Table.AddColumn(#"Added Risk Flag", "Quality", each if Text.Contains([Job], "Quality") then "Y" else "N"),
    #"Added Data Flag" = Table.AddColumn(#"Added Quality Flag", "Data", each if Text.Contains([Job], "Data") then "Y" else "N"),
    #"Added Information Flag" = Table.AddColumn(#"Added Data Flag", "Information", each if Text.Contains([Job], "Information") then "Y" else "N"),
    #"Added System Flag" = Table.AddColumn(#"Added Information Flag", "System", each if Text.Contains([Job], "System") then "Y" else "N"),
    #"Added Flow Flag" = Table.AddColumn(#"Added System Flag", "Flow", each if Text.Contains([Job], "Flow") then "Y" else "N"),
    #"Added Filter Jobs Flag" = Table.AddColumn(#"Added Flow Flag", "Filter Jobs", each if [BI] = "Y" or [Analyst] = "Y" or [Leadership] = "Y" or [PIF] = "Y" or [Risk] = "Y" or [Quality] = "Y" or [Data] = "Y" or [Information] = "Y" or [System] = "Y" or [Flow] = "Y" then "Y" else "N"),
    #"Filtered Relevant Jobs" = Table.SelectRows(#"Added Filter Jobs Flag", each ([Filter Jobs] = "Y")),
    #"Added New Brunswick Flag" = Table.AddColumn(#"Filtered Relevant Jobs", "N.B.", each if Text.Contains([Job], "(N-B)") then "Y" else "N"),
    #"Added Link Column" = Table.AddColumn(#"Added New Brunswick Flag", "Link", each "🔗"),
    #"Renamed Age Column" = Table.RenameColumns(#"Added Link Column", {{"Days Since Posted", "Age"}}),
    #"Converted Age to Integer" = Table.TransformColumnTypes(#"Renamed Age Column", {{"Age", Int64.Type}})
in
    #"Converted Age to Integer"

Creating a Date Table:

Using DAX in Power BI, I created a simple date table. Here’s the code:

_DateTable =
VAR StartDate = DATE(2024, 10, 1)
VAR EndDate = IF(TODAY() > DATE(2024, 12, 31), DATE(2024, 12, 31), TODAY())
RETURN
    ADDCOLUMNS(
        CALENDAR(StartDate, EndDate),
        "Year", YEAR([Date]),
        "Month", MONTH([Date]),
        "Day", DAY([Date]),
        "Month Name", FORMAT([Date], "MMMM"),
        "Quarter", "Q" & QUARTER([Date]),
        "Day of Week", WEEKDAY([Date], 2),  // Returns 1=Monday through 7=Sunday
        "Day Name", FORMAT([Date], "dddd")
    )

This date table allowed me to join the "JobBoardDate" column in both queries, enabling a unified date slicer for all report pages.

Building the Reports:

In Power BI, I created report pages for the GNB job postings and for each employer in the CareerBeacon dataset. Each report page includes filters for the job location and the the custom columns I created in Power Query (e.g., roles related to BI, data, leadership, etc.). Additionally, one of the custom columns in the CareerBeacon query calculates the "age" of each job posting (i.e., the number of days since it was posted). This ensures that jobs are sorted with the newest postings at the top.

What’s Next?

This Power BI integration has made my job monitoring system far more interactive and insightful. However, there’s always room for improvement. One challenge I’ve encountered with my current setup is that every day adds a new CSV file to the folder, which means that over time, the refresh process in Power BI becomes slower due to the growing number of files being loaded. To address this, I plan to explore potential solutions for my next version.

This iteration highlights the importance of refining and evolving a project to meet new goals. If you’re exploring automation or data visualization, I hope this inspires you to take your projects to the next level!