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 lvl3Main110001000-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-2200Sub102155 Main220002000-2999Main320002000-2499Main420002000-2200

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

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])))

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

It looks like you are correct!Can you provide your code?

Aerobat

Frequent Visitor

5 years ago

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:
Name	Number	Endnumber	Range
Main1	1000	1999	1000-1999
sub1	1020	1020
sub2	1030	1030
sub3	1040	1040
Main2	2000	2999	2000-2999
sub4	2010	2010
sub5	2020	2020
sub6	2030	2030
Main3	2000	2500	2000-2499
sub7	2410	2410
sub8	2420	2420
Main4	2000	2200	2000-2200
Sub9	2150	2150
Sub10	2155	2155



Result:
Name	Number	Range	Parent lvl 1	Number lvl 1	Range lvl1	Parent lvl 2	Number lvl 2	Range lvl2	Parent lvl 3	Number lvl 3	Range lvl3
Main1	1000	1000-1999
sub1	1020		Main1	1000	1000-1999
sub2	1030		Main1	1000	1000-1999
sub3	1040		Main1	1000	1000-1999
Main2	2000	2000-2999
sub4	2010		Main2	2000	2000-2999
sub5	2020		Main2	2000	2000-2999
sub6	2030		Main2	2000	2000-2999
Main3	2000	2000-2499	Main2	2000	2000-2999
sub7	2410		Main2	2000	2000-2999	Main3	2000	2000-2499
sub8	2420		Main2	2000	2000-2999	Main3	2000	2000-2499
Main4	2000	2000-2200	Main2	2000	2000-2999	Main3	2000	2000-2499
Sub9	2150		Main2	2000	2000-2999	Main3	2000	2000-2499	Main4	2000	2000-2200
Sub10	2155		Main2	2000	2000-2999	Main3	2000	2000-2499	Main4	2000	2000-2200

Need Help

Forum Discussion

Extracting a hierarchy from a range

12 Replies

Related content

Dynamic Y range visual calculation in two level hierarchy

Extract Data from sharepoint

Date hierarchy

Date Hierarchy

Data Extraction from Oracle