Hierarchy Problem Power Query

Power Query
1

Hi All,

I have a requirement where i need a solution slightly different from '" Hierarchies In Power BI Visualizations - Problem Of The Week " , to find the find the parent and child relation for all the components .
Hierarchy Problem 2.xlsx (20.9 KB)

Question Pic
Question Pic1278×748 207 KB

#8 (Power Query Solution),

2

Made a start here but I’m running out of day.
Maybe you could take the principles and finish off.

Logic centres around all continents and sub continents being in spelt with capital letters (Text.Upper) and all cities being spelt as Text.Proper, ie initial capital letters.

The attached gets close but the power query fill is filling the proper spelt words incorrectly.
You probably just need to do some more column concatenation to recreate lvl3 onwards so that countries all in one col and cities all in another. then don’t fill the cities

I think personally this is a bit messy, especially if table expands and the spelling convention is breached.
There will be other M gurus out there with a better approach I’m sure.
Hierarchy Problem 2 (1).xlsx (33.6 KB)

Sorry not to have given a better solution
Cheers
Pete

1 Like
3

Thanks Pete for your suggestions …

4

Hello @Unni , good to see that you are having progress with your inquiry. Did the response from @BINavPete help you solve your inquiry?

If it does, kindly mark as solution the answer that solved your query.

If not, how far did you get and what kind of help you need further?

5

Hi , i have not been able to make any progress with it , unable to achieve it with ideas provided.

6

Hi @Unni,

See if this meets your requirement.

let
    Source = Sample,
    ColNames = Table.ColumnNames( Source ),
    ReplaceValue = Table.Buffer( Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue, ColNames )),
    GetValues = List.Generate( ()=> 
            [ 
                t = Table.SelectRows( Table.FillDown( Table.Distinct( Table.SelectColumns( ReplaceValue, { ColNames{Count}, ColNames{Count+1} })), {ColNames{Count}}), each Record.Field(_, ColNames{Count+1}) <> null ), 
                Parent = Table.ToColumns( t ){0}, 
                Child = Table.ToColumns( t ){1}, 
                Count = 0 
            ],
        each [Count] < List.Count(ColNames) -1,
        each [
                t = Table.SelectRows( Table.FillDown( Table.Distinct( Table.SelectColumns( ReplaceValue, { ColNames{Count}, ColNames{Count+1} })), {ColNames{Count}}), each Record.Field(_, ColNames{Count+1}) <> null ),
                Parent = [Parent] & Table.ToColumns( t ){0},
                Child = [Child] & Table.ToColumns( t ){1},
                Count = [Count] +1
            ]
        ),
    ToTable = 
        Table.FromColumns( 
            { 
                List.LastN( GetValues, 1 ){0}[Parent], 
                List.LastN( GetValues, 1 ){0}[Child] 
            }, { "Parent", "Child" } 
        )
in
    ToTable

.
Returns this result.

image
image358×564 9.75 KB

.
Here’s your sample file.
eDNA - Hierarchy problem.pbix (17.4 KB)

I hope this is helpful

2 Likes
7

I believe this community is really lucky to have amazing experts like @Melissa , my mission is to figure out how this code works thanks a lot once again :smiley:

2 Likes