AI and Power BI

I mentioned a few posts ago that I was working on a data visualization project with another person (a finance director) to maintain our technical skills. This post will not detail all the elements that I have worked on but will focus mainly on the use of AI to help build the Power BI visuals.

But first, before I go into that, I need to bring up a big worry of mine: the degradation of my critical thinking skills and persistence. I do pride on my ability to work through hard problems and solve them that most finance professionals won’t do (at least the ones in my life experience).

That capability is what has allowed me to go home earlier, to dream up more interesting questions/approaches/insights, and to just generally keep the financial ship running smoothly.

And it has been reported that constant use of AI will degrade your critical thinking skills and your persistence to keep at the hard problems.

I’m not sure what to do about that because companies are requiring you to use AI in your work.

Damned if you do, damned if you don’t.

Using AI for Power BI questions

The way I look at Power BI, my value will not be in my technical skills in using Power BI. It will be in the innovative approaches of looking at things in Power BI, like stretching the boundaries of things and bringing new insights.

At least, that’s my goal.

As a comparison, I think of my use of spreadsheets, especially when I was starting out. While everyone was using these calculating machines, I was jumping into spreadsheets to help me “barrel balance” some inventory accounts. The result was I was leaving at 6 or 7 pm with my work done while everyone else was leaving at midnight with the accounts not balanced.

My value wasn’t in my adding abilities; my value was in figuring out how to balance the accounts cleanly every close and much quicker. I cleaned up the books. It wasn’t just that I knew how to use spreadsheets, which I did, but I knew how to use it beyond simple addition and subtraction.

Over the years I have managed to make very productive use of Excel. Who cares whether I’m doing math properly in my head or not.

That’s the same thing I want to approach with AI: being able to use AI very productively that I get massive advantage – maybe time savings or maybe doing stuff never done before. The trick is keeping my critical thinking skills and my judgement.

My goal with Power BI is not to be a technical expert in Power BI but to be able to deploy Power BI in extremely productive ways. And I need to use AI to help me develop sophisticated approaches to Power BI – to extend beyond what a normal finance person would do.

The first AI question

The project we’re doing is understanding and tracking unemployment numbers.

So first, we had to get the various definitions as there are various parts to the unemployment situation: like who actually gets classified and counted as unemployed. There’s the unemployed who are doing the job searching activities, but there are also those who are discouraged and thus not actively looking (and thus are not counted as unemployed). There are those participating in the labor force and those who are not.

So, there are different definitions and different ways of collecting data.

I first started with the U3 unemployment numbers which are the numbers quoted in the news. The unemployed people are those who are actively doing the job search. That means, those who are discouraged and have not looked in the last 4 weeks are not counted in the unemployed figures.

I’m looking for U3 numbers. I could do it for larger cities or major metropolis, could do it for states, or just for the entirety of the US.

We did want to get some state data but here’s the problem – well, not problem but a tedious thing: there are 50 states and you have to pull them individually 50 times and then merge the imports.

Unless you “program” Power BI to do the import on its own.

Now, I didn’t know that it was possible, but I thought, surely, there had to be a way to automate that because I was not looking forward to finding all of the URLs for the 50 states and pull them in individually.

AI told me that it was possible to automate the process.

The AI solution

I just asked CoPilot if it was possible to import data for 50 states without I having to do it 50 times and then merging the tables. And lickety split, CoPilot gave me the answer. Like, snap of the finger.

But there is one thing about AI, it often leaves out something, or it makes the wrong assumptions, or it misunderstands something, so if you get 80% of the way to the solution, I think you are doing good. For my problem it was 99% there. It left off the suggestion to name my function, but I recognized the issue just from having done programming or solving stuff like this.

The other thing CoPilot did was show me a better way of using the URL. I was copying the URL that was a long ugly version when CoPilot knew of a cleaner way to use the URL.

So here’s the URL I was getting when I right clicked on the Excel file to download:
https://fred.stlouisfed.org/graph/fredgraph.xls?bgcolor=%23ebf3fb&chart_type=line&drp=0&fo=open%20sans&graph_bgcolor=%23ffffff&height=450&mode=fred&recession_bars=on&txtcolor=%23444444&ts=12&tts=12&width=1320&nt=0&thu=0&trc=0&show_legend=yes&show_axis_titles=yes&show_tooltip=yes&id=TXUR&scale=left&cosd=1976-01-01&coed=2025-12-01&line_color=%230073e6&link_values=false&line_style=solid&mark_type=none&mw=3&lw=3&ost=-99999&oet=99999&mma=0&fml=a&fq=Monthly&fam=avg&fgst=lin&fgsnd=2020-02-01&line_index=1&transformation=lin&vintage_date=2026-03-29&revision_date=2026-03-29&nd=1976-01-01

An ungodly mess, wouldn’t you say?

By the way, the elements highlighted in yellow are the elements I would have to go in and change if I wanted to change the state (TXUR) or the latest date (2026-03-29).

What AI offered me was simpler:
url = “https://fred.stlouisfed.org/graph/fredgraph.xls?id=” & stateCode & “UR”,

stateCode was a variable pulled from a table containing codes for all 50 states. Notice I didn’t have to worry about dates.

Entire AI solution

So, the solution CoPilot gave me came in 3 parts: 1) the function that runs through the state codes and builds each state’s URL; 2) a table of the state codes; and 3) the query that does the actual importing of data.

1. Function

First you develop a function (rather than a query)

let
    GetStateUnemployment = (stateCode as text) =>
    let
        url = "https://fred.stlouisfed.org/graph/fredgraph.xls?id=" & stateCode & "UR",
        Source = Excel.Workbook(Web.Contents(url), null, true),
        Monthly = Source{[Item="Monthly",Kind="Sheet"]}[Data],
        Promoted = Table.PromoteHeaders(Monthly, [PromoteAllScalars=true]),
        Typed = Table.TransformColumnTypes(Promoted,{{"observation_date", type date}, {stateCode & "UR", type number}}),
        Renamed = Table.RenameColumns(Typed, {{stateCode & "UR", "Unemployment Rate"}}),
        AddedState = Table.AddColumn(Renamed, "State", each stateCode)
    in
        AddedState
in
    GetStateUnemployment

This coding in the advanced editor is the function. You need to name the function as GetStateUnemployment.

2. Table

This is interesting: you create a table of state 2-digit ids by building an array within brackets { }.

{"AL","AK","AZ","AR","CA","CO","CT","DE","FL","GA",
 "HI","ID","IL","IN","IA","KS","KY","LA","ME","MD",
 "MA","MI","MN","MS","MO","MT","NE","NV","NH","NJ",
 "NM","NY","NC","ND","OH","OK","OR","PA","RI","SC",
 "SD","TN","TX","UT","VT","VA","WA","WV","WI","WY"}

This table is to be named StateCodes. Yes, the state is capitalized.

3. Query

Finally, the usual query looks like this:

let
    Source = "StateCodes",
    FetchAll = List.Transform(Source, each "GetStateUnemployment"(_)),
    Combined = Table.Combine(FetchAll),
in
    Combined

This query you can name whatever you want.

What I ended in the Query section

I ended up with something similar to the image below. Now, in the image you will see folders which was done by creating folders.

The “HTML Code for dates 1” is something else. That might be tomorrow night’s topic because these involved pulling information from the government site.

Closing

CoPilot was a big help in cutting down my effort to pull in 50 datasets from the FRED site. I would have never figured this out since I do not know M or dax that much. Did I lose critical thinking skills? I don’t know. If I couldn’t use AI, I would have had to use Google to search the web for solutions and read through numerous sites to get at a solution.

I just don’t know if I’m losing my thinking skills. I hope not.