How do the top 10 companies in GA and their respective tax payment in 2018 line up? The original list comes from Forbes.com where they rank the top companies in the world. The rank is based on factors such as sales, profits, assets and market value: (https://www.forbes.com/global2000/list/#country:United%20States_state:Georgia).
For Nested Queries, feel free to skip down below to "What's the M Code?".
First, I found it interesting that the 7 of the top 10 companies in GA fall under the top 400 companies in the world. Go GA!
The size of the boxes represents the tax paid. The vertical Y axis shows the GA rank, and the horizontal X axis shows the World Rank.
Who Paid The Most In Taxes?
Another interesting thing to point out is that the top company for GA, Home Depot, did not pay the most in taxes. Our good friends over at Coca-Cola actually ended up paying the most.
By the way, I calculated the average tax % is from their 2017 tax expense divided by their 2017 gross profit, which I gathered from Finance.Yahoo.com.
Average Tax = (Tax Expense / Gross Profit)
For companies that had a tax refund this year, I used the average of the last three years.
Alien Note: It's important to understand that average tax does not equal tax bracket. A company's actual tax bracket will be higher than what an average tax calculation yields.
What About Other States?
What would this graph look like with more states on it? The following image represents states that have at least 10 companies on the list (without the size factor for tax).
That looks a bit messy. Let's add some lines to add some definition to it:
Still kinda messy... Okay, but what about the top 5 of those states (can you guess which one's they are?:
In order of rank:
California
New York
Illinois
Texas
Georgia
This means that (at least) 50 of the worlds top companies come from these states! And Georgia made it in the top 5 for the US!
Below, I'll talk about how I gathered the data with Power Query.
- What's the M Code? -
I used the Web connector to first connect to Forbes.com and grab the list. You can read my other blog about connecting to the web here: Power-BI-Ultimate-Guide-to-the-Basics-of-Importing-Data
Nested "let...in..." Queries
What I want to mention here is the technique of nesting a query within a query. In other words, implanting a "let...In..." within another "let...In...". I haven't seen this explained very often on other sites, so I'd like to take the time go explain it here. It looks something like this:
let Step1 = 1, Step2 = let Step2_sub1 = 2, Step2_sub2 = Step2_sub1 * 3 in Step2_sub2,
Step3 = Step1 + Step2 in Step3
In the above example code, the query spits out Step3, which results in the value 7.
Step3 equals Step1 plus Step2, which is:
Step3 = (1) + (2 * 3)
Step2 is our nested query which evaluates to:
Step2 = (2 * 3)
The most confusing part for me is the placement of commas. But if you look at each section as blocks, it becomes clear what is going on. You'll notice there are two commas in the "main blocks"; One after Step1 and another after Step2. Step3 doesn't need one since it's the last step in the query.
Why Do This?
M programming let's you create variables by assigning names with functions, which creates Steps. For example:
Source = 1
or
Source = Excel.Workbook()
But what if you wanted to make "Source" multiple steps? By nesting queries, you can essentially have one step consist of multiple "sub" steps. This is particularly usefully if you need to perform steps only if a prior condition exists.
A More Specific Example
Let say you have a step that sometimes results in an error, but you have other steps after that. Obviously if a step results in an error, the steps following will not work.
To solve this problem, you can store these steps (the step that sometimes results in an error plus the additional ones) in a nested query variable. You can then create a step to test for the condition of an error. If the condition is true, return the nested query, else return some other value (or other nested query).
The Nested Query I Used For This Project
Here is the code I used which contains a nested query:
let Source = Web.Page(Web.Contents("Forbes URL.com")), test = if Source{0}[Id] = "the_list" then 1 else 0, returnRanks = let Data0 = Source{0}[Data], #"RemoveColumns" = Table.SelectColumns(
Data0,{"Rank", "Company"}), #"Added Index" = Table.AddIndexColumn(
#"RemoveColumns", "State Rank", 1, 1) in #"Added Index", returnTable = if test = 0 then test else returnRanks in returnTable
In the above code, "returnRanks" is the nested query, which sometimes results in an error. My variable "test" tests for this condition. This nested query never gets executed if my variable "test" results in 0. This therefore prevents an error from resulting in my overall query.
Can't I just Create Another Query to Reference?
Yes, but...
The good thing about using nested queries like this, is that you can use variables created outside of the nested query inside of it. For example, take the code I used above. Source is created in the main query, and is also referenced in the nested query. I didn't need to pass in the variable via a custom function.
So in other words, the advantages of using nested queries is to:
Avoid having to create custom functions.
Avoid having to manage additional queries in the query editor.
And most importantly, to impress your co-workers with slightly more advanced looking M code.
I hope you learned something! Let me know in the comments what your thoughts are!
