Problem of the Week #8 (PQ) - Hierarchy Problem

[alexbadiu]

OK, here we go!
Problem of the Week #8 is now live! The YouTube video introducing the problem can be found here

For background on this initiative, check out this post .

Background

Working on hierarchies and putting them in the desired shape inside Power Bi is a very common use case. This problem of the week is inspired from a real life use case.
Knowing how to solve it in Power Query is important, as it will facilitate your report development.

The Model

The model only contains one table.

Your Task

The task for this challenge is to take the data from the excel file and put it in a shape that will allow you build the following map visualization. In order to achieve this you will need 2 columns: the name of the countries and the corresponding cities.

How to approach the problem?

My recommendation for this challenge is to take the time to look at the data and have a global strategy of how to attack the problem before starting to work on the solution.

So, now back to you

Can you crack this?

Full instructions are on the YouTube video Brian & I did for this Problem of the Week.

Important
We ask that if you post anything directly related to a solution to please use the “Hide Details” or “Blur Spoiler” options within your post. This will prevent people who have not yet completed their solution from seeing things they might not want to see yet and could influence their solution.

To use these options, just select the text or image you want to hide, click on the gear icon on the top right at the top of your post and select either Hide Details or Blur Spoiler.

Note that this is a Power Query only challenge, so no DAX even if that is what you would choose to use outside of this challenge.

Eligibility for the Drawing
To encourage participation, we will be doing a drawing of five random participants each round to receive one of these cool Enterprise DNA vinyl laptop stickers:

To be eligible for the drawing, just send an email to problemoftheweek@enterprisedna.co before 11:59pm ET, Wednesday, March 24, 2021 indicating you’ve successfully completed the challenge, and please provide your mailing address for us to send the sticker if you’re selected.

We hope you find this initiative fun, challenging and helpful in continuing to build your Power BI skills. All of the information you will need is contained in the files within this post. If you have any questions or comments, just message @BrianJ or @alexbadiu in this forum thread.

Good luck, and enjoy!!!

P.S. Your feedback is always welcome, and if you have a real-world problem that you think might make a good future Problem of the Week, please email it to us at problemoftheweek@enterprisedna.co.
Thanks!

Here are the files you will need:
Hierarchy Problem.xlsx (17.5 KB) POTW # 8 - Hierarchy Problem.pbix (2.8 MB) world-countries.json (98.2 KB)

[Matthias]

Looks as if I am the first. After some trial and error it looks as requested and no DAX was used:

Summary

Hierarchy Problem.pbix (54.4 KB)

[alexbadiu]

Great solution Matthias! In order to complete the full request, do not forget to also add the map

[Melissa]

Hi @alexbadiu,

Thanks for hosting this weeks challenge!
Great pratical problem. Had fun working through it.

Click here to View my solution.

This is just one of those times where it comes in handy that Power Query is case sensitive
First I added a Custom Column for the Country.

Table.FillDown(
  Table.AddColumn( Source, "Country", 
    each 
      let
        myValue = List.RemoveNulls(Record.FieldValues(_)){0}?
      in
        if Text.Upper(myValue) = myValue then myValue else null,
    type text
  ),
  {"Country"}
)

an alternative could be something like:
if Text.Length( Text.Remove(myValue, {"A".."Z"})) =0 then myValue else null

.
Next I added a Custom Column for the City where I could filter out blanks.

Table.SelectRows(
  Table.AddColumn( AddCountry, "City",
    each 
      let
        myValue = List.RemoveNulls(Record.FieldValues(_)){0}?
      in
        if Text.Upper(myValue) <> myValue then myValue else null,
    type text
  )[[Country], [City]],
  each ([City] <> null)
)

Translating that to the alternative would look something like:
if Text.Length( Text.Remove(myValue, {"A".."Z"} & {"/", " "})) <>0 then myValue else null

Note that you’ll have to expand the list with additional characters when present. So testing if the Text.Upper(myValue) = myValue is by far the best option.

.
Loved using the shape map I never really used that before. So that was fun.

.

Here’s my solution file.
POTW # 8 - Solution Melissa.pbix (2.9 MB)

[Matthias]

@alexbadiu Thanks Alex, so I missed the real challenge. And that’s where the JSON is used for.
I couldn’t really see what that JSON data was good for.

[Jihwan]

Hi, @alexbadiu,
I think I created more problems in my pbix file by solving this.
I am not very good at writing M- language. So I tried to solve it by only clicking functions in Power Query Editor. As a result, Power Query Editor in my pbix file became too messy.

I thought there might be a way to solve the problem in a nicer way even I just only clicked functions in Power Query Editor. But so far, I have not yet found a way, so I submit this one first. Once I find a nicer way, then please allow me to resubmit.

By the way, one thing I noted was that the country name “Korea” could not be categorized under Country Name. So, I changed it to “South Korea”, not because I am from South Korea, but because the city name is Seoul.

Thank you very much for the great content.

POTW # 8 - Hierarchy Problem (Jihwan Kim).pbix (2.9 MB)

[alexbadiu]

Hello @Jihwan ! If you need to improve your M skills, you are in the right place. The advantage of the POTW is that not only you can challenge your skills in order to get to the result and therefore practice, but you can also leverage the skills of the other members by looking and downloading their solutions at the end. There will always be something new to learn. I am already impressed by the solutions provided so far, and every single solution is completely different ! So even more room to learn something new.
In which concerns Korea, you are completely right. It is not shown on the map if it is not changed to the correct name: “South Korea”. But I am glad you mentioned it. There are always surprises when you work with data because data is not always clean, naturally. We should develop our critic eye and always check

[BrianJ]

@alexbadiu,

Awesome problem. Really enjoyed this one.

Trying to refine my “bull in a china shop” Power Query style, so really focused on efficiency and minimizing my steps. Was feeling pretty good about this until I saw @Melissa was able to shave 8 more steps off the total. Still a lot to learn… , but improving week by week.

Thanks for leading this round!

POTW # 8 - Hierarchy Problem - Brian Julius Solution.pbix (2.9 MB)

[BrianJ]

@Melissa,

This is just so good. I used the same basic logic in my solution, but your application of it is so much more efficient. I’m trying to replicate this and have one question on the mechanics of how you built this out. Did you do this in pieces with a separate “dummy” query used to develop and test the individual pieces of your city and country steps, and then once you confirmed those, copied them back into your main query?

Great stuff!

– Brian

[Melissa]

@BrianJ

Did you do this in pieces with a separate “dummy” query used to develop and test the individual pieces of your city and country steps, and then once you confirmed those, copied them back into your main query?

No, not this time around Brian.

Read on....

I wrote the logic from the inside out, adding a function here or there directly in the formula bar. Also wanted to keep the code clean so used varables for that and this problem didn’t require that much logic.

Let’s look at the most elaborate one, adding the “City” column and review it step by step:

1. Add a Custom Column and enter this code in the dialog box
   
   

• Record.FieldValues(_) returns a list with all field values in that row as a list
• There is just one text sting in each row (before I added the Country column, that is)
• Wrapping List.RemoveNulls( ) around it means I’m only left with that string
• I can extract that string by it’s position and because I removed all blanks that is the first position in a zero-base indexed list {0}
• To avoid errors in empty rows (so empty lists) added the ? to return a null instead if the element is not present.

2. In the formula bar added the type declaration:
   

   

3. Added my column selection
   

   

4. And wrapped Table,SelectRows around it
   

   

[Matthias]

2nd and improved solution. Same idea, but simplified with considerably less steps.

Summary

let
    Source = Before,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Company", "Lvl1"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Lvl2", "Lvl3", "Lvl4", "Lvl5", "Lvl6"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"City"),
    #"Filtered blank EUROPE W/O" = Table.SelectRows(#"Merged Columns", each [City]<>"" and not Text.Contains([City], "EUROPE") and not Text.Contains([City], "W/O")),
    #"Added Country" = Table.AddColumn(#"Filtered blank EUROPE W/O", "Country", each if Text.Upper([City])=[City] then [City] else null),
    #"Filled Down" = Table.FillDown(#"Added Country",{"Country"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [City] <> [Country])
in
    #"Filtered Rows"

[Matthias]

@JiHwan I admire people who know how to code, but I think the best strategy you can follow is to use the UI as much as possible and let it write the code for you. And then adapt when necessary the code in the function bar a little bit. By this you can rely that the syntax is correct, and you do not have to be “very good at writing M-language”.

[Jihwan]

Hi, @Matthias,
Thank you very much for your advice.
I am keeping using UI as much as possible. And now, I am trying to learn how to use dynamic parameters in Power Query Editor, and I am clicking buttons a lot.

[BrianJ]

@Melissa,

Thanks so much for the additional explanation and illustrations. That post definitely get a bookmark – extremely helpful in improving my M coding to better understand both the thought processes and mechanics you use in getting to these awesome solutions.

• Brian

[quantumudit]

Here goes my solution for the Week-08 challenge.

Week08_Hierarchy_Problem_PowerQuery_Udit.pbix (2.9 MB)

My approach is based on the case of the different hierarchy level (except “Cities”, all other hierarchy levels are in the upper case in the source file)

Nothing is hardcoded so, as long as the source format remains the same; the M-script can handle future updates too.

Also tried to add some dashboarding style to the result

I was overloaded with office works for the last few weeks and thus, couldn’t able to submit the Week-06 and Week-07 challenge on time but, that’s on my to-do list, and will do it very soon.

Again, thanks a lot for the amazing challenges @alexbadiu and @BrianJ

[chrish]

Thank you for the problem. Like all good puzzles it had me scratching my head for a while and then I had an a-ha moment and found a fairly simple solution:

As for others (I now see having read the thread) my a-ha moment was realising that I could use the case sensitivity of M code to distinguish between cities (in lower case other than the initial capital letter) and other levels of the hierarchy all in upper case. I reckoned that all cities must have at least one vowel after their initial capital (that’s now got me thinking of exceptions - eg Omsk - but at least they all do within the data provided) so was able to split cities and non-cities into separate columns with a couple of conditional column lines in the UI. Countries always immediately preceded cities in the hierarchy so I could fill down my non-city column and then filter to remove any information that didn’t match a city. Removing the redundant columns left a nice clean Country : City pair table.

As ever with these challenges I discovered something new by activating the shape map in preview. I’m sure I’ll be able to find a use for that in the day job.

Copy of the pbix file attached for reference.
POTW # 8 - Hierarchy Problem.pbix (2.9 MB)

Thanks again for the problem. Have a good week.

Chris

[jbressan]

Hi, @alexbadiu

This is my solution for POTW#8.

POTW # 8 - Hierarchy Problem-Jbressan.pbix (2.4 MB)

[alexbadiu]

Hello all,

Here we are at the end of POTW # 8! The time passed so fast!
It was a great experience organizing this round and I hope you enjoyed it too!
I’ve chosen this topic for two main reasons :

1. There are high chances that one day you will encounter a similar situation in your work. Therefore, this exercise will arm you with the best techniques to implement a solution
2. The use of Power Query to put in shape your data for data visualization. In order to simplify your work in DAX and increase the performances of your model, a good knowledge of Power Query is a must!

I really enjoyed seeing how you approached the problem and it was very interesting to see the different solutions. This diversity of problem solving is really incredible!

The approach I took for solving this challenge is yet again different than the other solutions. In the original dataset, the uppercase, lowercase couldn’t be used as the dataset was bigger than the one provided in the excel and the updates were frequent. I chose therefore an approach where I use the previous row in order to arrive to the solution.

All the steps are explained in the video published today:

Hierarchies In Power BI Visualizations - Problem Of The Week #8 (Power Query Solution)

STAY TUNED FOR POTW 9!

Until next time,
Best regards,
Alex Badiu

POTW # 8 - Hierarchy Solution.pbix (2.9 MB)

[Matthias]

@alexbadiu nice approach to unpivot if there are too many cases to filter out the exceptions

This alternative is a bit longer than my second solution, but still pretty short:

let
    Source = Before,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Company"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Level", "Country"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Lvl","",Replacer.ReplaceText,{"Level"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Level", Int64.Type}, {"Country", type text}}),
    Shifted = Table.FromColumns(
Table.ToColumns(#"Changed Type") & {List.RemoveFirstN(#"Changed Type"[Level],1)},
Table.ColumnNames(#"Changed Type") & {"Up"}),
    #"Added City" = Table.AddColumn(Shifted, "City", each try if [Level]<[Up] then null else [Country] otherwise [Country], type text),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added City",{"Country", "City"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Removed Other Columns1",each if [Country]=[City] then [Country] else null,null,Replacer.ReplaceValue,{"Country"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value1",{"Country"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([City] <> null))
in
    #"Filtered Rows"

[alexbadiu]

@Matthias, this is an awesome solution!