cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Extracting a hierarchy from a range

I already posted a similar problem wher I got some fine help from this amazing community. 
Now I have developed things a bit further and need to extract the hierarchy from a range and the value a member has.

For each row I want to know the hierachy above. Se example below. (I'm still a beginner)

Start:           
NameNumberEndnumberRange        
Main1100019991000-1999        
sub110201020         
sub210301030         
sub310401040         
Main2200029992000-2999        
sub420102010         
sub520202020         
sub620302030         
Main3200025002000-2499        
sub724102410         
sub824202420         
Main4200022002000-2200        
Sub921502150         
Sub1021552155         
            
            
            
Result:           
NameNumberRangeParent lvl 1Number lvl 1Range lvl1Parent lvl 2Number lvl 2Range lvl2Parent lvl 3Number lvl 3Range lvl3
Main110001000-1999         
sub11020 Main110001000-1999      
sub21030 Main110001000-1999      
sub31040 Main110001000-1999      
Main220002000-2999         
sub42010 Main220002000-2999      
sub52020 Main220002000-2999      
sub62030 Main220002000-2999      
Main320002000-2499Main220002000-2999      
sub72410 Main220002000-2999Main320002000-2499   
sub82420 Main220002000-2999Main320002000-2499   
Main420002000-2200Main220002000-2999Main320002000-2499   
Sub92150 Main220002000-2999Main320002000-2499Main420002000-2200
Sub102155 Main220002000-2999Main320002000-2499Main420002000-2200
12 REPLIES 12
Frequent Visitor

Nobody?

Impactful Individual
Impactful Individual

Hi, @Aerobat 

There are some discrepancies between my code and the simulated data you provided, my code runs as shown below:

22.png

Did you have the code that gave the result above?

Impactful Individual
Impactful Individual

@Aerobat 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("fZCxDsIgEEB/xdxMEzgOle6OdtDROLSxMQ5l0HQy/rulSQ13FZYOR9/jHpc3NO3QQw3H9hEMKGjGoeufUButtYJDuIVl4L1XcGrDPf4ej6s4go/6OV5jxxUoFPNgUWwEigy1ErUF1DKUJEo5NEan16KMRhYdjytcRxNTGKGYB7nNHUNRoqX32jLUSjT7XjHaFqOd1jKaVtG7VEEymkrRe4bKaMpGx82puDmuN5++qeM8dj5VGCcU8+Dv9RNqNGedZB1jr18=", BinaryEncoding.Base64),Compression.Deflate))),
    fnTrans = (x,y)=>
        let ft= Table.RemoveLastN(Table.RemoveColumns(Table.SelectRows(filter, each [Number]<= x and [Endnumber] >=x), "Endnumber"), (r)=>r[Name]=y), 
            fv=Table.FirstValue(ft)=y, 
            cmb=if fv then {} else List.Combine(Table.ToRows(ft)) 
        in cmb,
    filter = Table.Buffer(Table.SelectRows(Source, each Text.StartsWith([Name], "M"))),
    trans = Table.AddColumn(Table.RemoveColumns(Source, "Endnumber"), "t", each fnTrans([Number], [Name])),
    meg = Table.CombineColumns(trans, Table.ColumnNames(trans), each List.RemoveLastN(_)&_{3}, "t")[t],
    result = let 
             zip=List.Zip(meg), 
             fd={"Name", "Number", "Range"}&
                 List.TransformMany(
                     {1..(List.Count(zip)-3)/3}, 
                     each {"Parent lvl", "Number lvl", "Range lvl"}, 
                     (x,y)=>y&Text.From(x)
                 ), 
             tbl=Table.FromColumns(zip, fd) 
          in tbl
in
    result

Hi,

 

this row:

filter = Table.Buffer(Table.SelectRows(Source, each Text.StartsWith([Name], "M"))),

It assumes headlines starts with "M", but it can start with any letter.

I tested with part of my table. Unfortunately result is not as I intented.
Se code below and my result.
Maybe it is caused my bad description.

It seems I get ALL parents in a row. But in reverse order.

I think problem is that a leaf can be in several branches.
It is maybe just not possible to get it the way I want (full hierachy for each record on each row).

So maybe you could hal with a more simple solution.

1. Search should be done for each row regardless of if it has a range or if it is "single/leaf"

2. Search should be done from last row upwards in table. ( Table is sorted correct)

3. First occurance of record with range is regarded parent

4. Table is extended with Parent Name, Number and Range

 

 

This is code I tried (I tried both versions with similar results). In it you can find my more authetic data.

let
Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVNBcoIwFL1KxnWZIorVJQ7IUC2dQezGcRFNYKIWOwG8QT2DJ/AM3edizQ8gkZl29V4m7+W/n/ys170gI8WeZixL8y0jHKe9p17fNE0Jw8HEqleG4pundc9JDHRKUUZJfuDsDEbKEU4Qzo7ipruHk9HdDRzcEc3LY4ELlIgfjjjNCso1i23bdwtwZfGWq0XsxGgmrhFazp1YM4wVVAbgYBDfnBa5PLwqpaknw8ldDRzUKwJRCoT5lu4Jhf1+3+wACN/fluISh0Hoo8jzAZdONPVeXU8XDxqPofijURNa41YIHISuuMzVudPAjRxNbZkvd7XinTxxsJiu3Ic4o8pQVxlBnLpKzI7bkuR1vxwZCD/vdHELIA9SnKVncZNvRSjSXF+cfZ5UK3VHLSif74T+h7hEXuh6qI4W6UKr0RuK/1tMvmRTzTI7oKYydCMPBaEbi4sf13UGtnbTctXetJOkLJGjB5NcsGMzuNbQ7ICapyunOOFlRmDY1Y5tdgB0c5znlLAkoTL+TsVm8nPJs9UMWtWTaPCHqSQQTomqZ9cALDN8KEqOOd7Kf2SggwxWfaKm0xY2m18=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, Endnumber = _t, Range = _t]),
Source = Table.TransformColumnTypes(Source1,{{"Name", type text}, {"Number", Int64.Type}, {"Endnumber", Int64.Type}, {"Range", type text}}),
A = Table.AddColumn(Source,"m",each
let
a=Table.ToRows(Table.SelectRows(Table.FirstN(Source,List.PositionOf(Source[Name],[Name])),(x)=>x[Range]<>null)),
b=List.Transform(a,(y)=>if [Number]<=y{2} and [Number]>=y{1} then List.RemoveRange(y,2,1) else {})
in
List.RemoveRange(Record.ToList(_),2,1)&List.Combine(b)
),
Result = Table.FromList(A[m],each _,List.Count(List.Last(A[m])))

in
Result

 

 

Code in better format

let
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVNBcoIwFL1KxnWZIorVJQ7IUC2dQezGcRFNYKIWOwG8QT2DJ/AM3edizQ8gkZl29V4m7+W/n/ys170gI8WeZixL8y0jHKe9p17fNE0Jw8HEqleG4pundc9JDHRKUUZJfuDsDEbKEU4Qzo7ipruHk9HdDRzcEc3LY4ELlIgfjjjNCso1i23bdwtwZfGWq0XsxGgmrhFazp1YM4wVVAbgYBDfnBa5PLwqpaknw8ldDRzUKwJRCoT5lu4Jhf1+3+wACN/fluISh0Hoo8jzAZdONPVeXU8XDxqPofijURNa41YIHISuuMzVudPAjRxNbZkvd7XinTxxsJiu3Ic4o8pQVxlBnLpKzI7bkuR1vxwZCD/vdHELIA9SnKVncZNvRSjSXF+cfZ5UK3VHLSif74T+h7hEXuh6qI4W6UKr0RuK/1tMvmRTzTI7oKYydCMPBaEbi4sf13UGtnbTctXetJOkLJGjB5NcsGMzuNbQ7ICapyunOOFlRmDY1Y5tdgB0c5znlLAkoTL+TsVm8nPJs9UMWtWTaPCHqSQQTomqZ9cALDN8KEqOOd7Kf2SggwxWfaKm0xY2m18=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, Endnumber = _t, Range = _t]),
    Source = Table.TransformColumnTypes(Source1,{{"Name", type text}, {"Number", Int64.Type}, {"Endnumber", Int64.Type}, {"Range", type text}}),
    A = Table.AddColumn(Source,"m",each
                let
                    a=Table.ToRows(Table.SelectRows(Table.FirstN(Source,List.PositionOf(Source[Name],[Name])),(x)=>x[Range]<>null)),
                    b=List.Transform(a,(y)=>if [Number]<=y{2} and [Number]>=y{1} then List.RemoveRange(y,2,1) else {})
                in
                    List.RemoveRange(Record.ToList(_),2,1)&List.Combine(b)
                             ),
    Result = Table.FromList(A[m],each _,List.Count(List.Last(A[m])))                             

in
    Result

In order to better understand your case, could you post a picture of the expected result of below table?

1.png

I think it should be something like this:

NameNumberEndnumberRangeParentParent NumberParent EndnumberParent Range
Årets resultat100049491000-4949    
RESULTAT FØR SKAT100048001000-4800Årets resultat100049491000-4949
Resultat før renter100045551000-4555RESULTAT FØR SKAT100048001000-4800
Af- og nedskrivninger af anlæg100044961000-4496Resultat før renter100045551000-4555
Indtjeningsbidrag100043921000-4392Af- og nedskrivninger af anlæg100044961000-4496
DÆKNINGSBIDRAG111020701110-2070Indtjeningsbidrag100043921000-4392
OMSÆTNING111012801110-1280DÆKNINGSBIDRAG111020701110-2070
OMSÆTNING REGNINGSARBEJDE111011301110-1130OMSÆTNING111012801110-1280
Udført arbejde111011101110OMSÆTNING REGNINGSARBEJDE111011301110-1130
OMSÆTNING TILBUDSARBEJDE116011801160-1180OMSÆTNING111012801110-1280
Tilbudsarbejder - a/c118011801180OMSÆTNING TILBUDSARBEJDE116011801160-1180
IGANGVÆRENDE ARBEJDER121012201210-1220OMSÆTNING111012801110-1280
Igangværende arbejder - primo121012101210IGANGVÆRENDE ARBEJDER121012201210-1220
Igangværende arbejder - ultimo122012201220IGANGVÆRENDE ARBEJDER121012201210-1220
ANDRE INDTÆGTER123512801235-1280OMSÆTNING111012801110-1280
Afgifter og tillæg124012401240ANDRE INDTÆGTER123512801235-1280
Øreafrundning125012501250ANDRE INDTÆGTER123512801235-1280
Kassedifferencer - indtægt126012601260ANDRE INDTÆGTER123512801235-1280
Kassedifferencer - udgift127012701270ANDRE INDTÆGTER123512801235-1280
Fakturarabat - kunder128012801280ANDRE INDTÆGTER123512801235-1280

Please make it clear of what is your expected result, my code generates a table like you provided in post #1.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    A = Table.AddColumn(Source,"m",each
                let
                    a=Table.ToRows(Table.SelectRows(Table.FirstN(Source,List.PositionOf(Source[Name],[Name])),(x)=>x[Range]<>null)),
                    b=List.Transform(a,(y)=>if [Number]<=y{2} and [Number]>=y{1} then List.RemoveRange(y,2,1) else {})
                in
                    List.RemoveRange(Record.ToList(_),2,1)&List.Combine(b)
                             )
in
    Table.FromList(A[m],each _,List.Count(List.Last(A[m])))

 

 

1.png

Impactful Individual
Impactful Individual

@Aerobat 

Sorry, the code isn't available at the moment, I've put the file on my company computer.I'll go to my office tomorrow and post it up for you.

It looks like you are correct!

Can you provide your code?

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors