|Cannot Create Relationship|
|Users (Bad Headers)|
|Use First Row As Headers|
|Users (Good Headers)|
Next, let's summarize the Users (2) table to show the Number of Regions per Manager. We can accomplish by clicking the "Group By" button the Ribbon.
|Number of Regions|
|Users (Number of Regions)|
|Users (Merged, Dirty)|
Currently, we can't see the information that was merged into this table. So, we need to expand the "Table" by clicking on the "Double Arrow" at the top of the NewColumn and selecting the "Number of Regions" checkbox in the "Expand" panel. Also, it's cleaner if you deselect the "Use Original Column Name as Prefix" option.
|Expand Number of Regions|
This leaves us with a table that looks more traditional.
|Users (Merged, Clean)|
Now, we have a way to identify who is a CEO and who is a Manager. So, let's create a calculated column that shows that. We can do this by selecting the "Add Custom Column" button in the "Add Column" Ribbon.
|Add Custom Column|
Next, we need to define our calculation. The language behind Power Query is formerly known as "M". You can find a formula library here. We want to create a calculation called "Level" that returns "CEO" if "Number of Regions" equals 4, and returns "Manager" otherwise.
This calculation adds the "Level" column to the Users table.
|Users (with Level)|
Here's where we get to the tricky part. We want to transform this table so that it contains a column for Manager and a column for CEO. The manager column should have the appropriate manager based on the region, while the CEO column always has Pat. We can accomplish this by selecting the "Level" column, then clicking the "Pivot Column" button in the "Transform" Ribbon.
Now, we've already told Power Query that we want to create columns based on the values in the "Level" column. However, we still need to tell it what values should go in the new columns. We want the new columns to take the non-aggregated values from the Manager column, i.e. the Manager Names.
|Pivot Level with Manager|
Now, our table has quite a few holes.
|Users (with Holes)|
Most programming languages, including SQL, will aggregate values when you perform a pivot to remove holes like this. Unfortunately, Power Query does not. There are a number of ways we could accomplish this. We think the easiest way is to select the "CEO" column, then select the "Fill Up" button in the "Transform" Ribbon.
This will fill in the gaps in the CEO column. We could use "Fill Down" to fill the gaps in the Manager column as well. However, we don't need to. We're only interested in having a single row for each Region, with the corresponding Manager and CEO listed. Looking down the list, we see that we already have these 4 rows available.
So, all we have to do is filter the "Number of Regions" column to only include 1. We can do that by clicking the Triangle at the top of the column, and deselecting the "4" box.
|Filter Number of Regions|
Now, our table looks almost ready.
All that's left to do is the remove the "Number of Regions" column because we no longer need it. We can do that by selecting the "Number of Regions" column and clicking the "Remove Columns" button from the "Home" Ribbon.
Now, our table is complete and we can use it to further our analysis.
The interesting thing about this procedure is that it can likely be done in a myriad of other ways. Power Query even has an advanced editor where you could probably code an approach to handle this automatically. We definitely didn't take the easy way out for accomplishing this. To see that, check out our previous post. All in all, we feel this approach is pretty flexible, but lacks some of the more advanced features. For instance, what if there were 3 levels of hierarchy in our instead of just the 2 we had here (Manager and CEO)? Could we create some sort of iterative process to accomplish this? Maybe someone in the comments can help us out. Thanks for reading. We hope you found this informative.