Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Nobody?
Hi, @Aerobat
There are some discrepancies between my code and the simulated data you provided, my code runs as shown below:
Did you have the code that gave the result above?
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?
I think it should be something like this:
Name | Number | Endnumber | Range | Parent | Parent Number | Parent Endnumber | Parent Range |
Årets resultat | 1000 | 4949 | 1000-4949 | ||||
RESULTAT FØR SKAT | 1000 | 4800 | 1000-4800 | Årets resultat | 1000 | 4949 | 1000-4949 |
Resultat før renter | 1000 | 4555 | 1000-4555 | RESULTAT FØR SKAT | 1000 | 4800 | 1000-4800 |
Af- og nedskrivninger af anlæg | 1000 | 4496 | 1000-4496 | Resultat før renter | 1000 | 4555 | 1000-4555 |
Indtjeningsbidrag | 1000 | 4392 | 1000-4392 | Af- og nedskrivninger af anlæg | 1000 | 4496 | 1000-4496 |
DÆKNINGSBIDRAG | 1110 | 2070 | 1110-2070 | Indtjeningsbidrag | 1000 | 4392 | 1000-4392 |
OMSÆTNING | 1110 | 1280 | 1110-1280 | DÆKNINGSBIDRAG | 1110 | 2070 | 1110-2070 |
OMSÆTNING REGNINGSARBEJDE | 1110 | 1130 | 1110-1130 | OMSÆTNING | 1110 | 1280 | 1110-1280 |
Udført arbejde | 1110 | 1110 | 1110 | OMSÆTNING REGNINGSARBEJDE | 1110 | 1130 | 1110-1130 |
OMSÆTNING TILBUDSARBEJDE | 1160 | 1180 | 1160-1180 | OMSÆTNING | 1110 | 1280 | 1110-1280 |
Tilbudsarbejder - a/c | 1180 | 1180 | 1180 | OMSÆTNING TILBUDSARBEJDE | 1160 | 1180 | 1160-1180 |
IGANGVÆRENDE ARBEJDER | 1210 | 1220 | 1210-1220 | OMSÆTNING | 1110 | 1280 | 1110-1280 |
Igangværende arbejder - primo | 1210 | 1210 | 1210 | IGANGVÆRENDE ARBEJDER | 1210 | 1220 | 1210-1220 |
Igangværende arbejder - ultimo | 1220 | 1220 | 1220 | IGANGVÆRENDE ARBEJDER | 1210 | 1220 | 1210-1220 |
ANDRE INDTÆGTER | 1235 | 1280 | 1235-1280 | OMSÆTNING | 1110 | 1280 | 1110-1280 |
Afgifter og tillæg | 1240 | 1240 | 1240 | ANDRE INDTÆGTER | 1235 | 1280 | 1235-1280 |
Øreafrundning | 1250 | 1250 | 1250 | ANDRE INDTÆGTER | 1235 | 1280 | 1235-1280 |
Kassedifferencer - indtægt | 1260 | 1260 | 1260 | ANDRE INDTÆGTER | 1235 | 1280 | 1235-1280 |
Kassedifferencer - udgift | 1270 | 1270 | 1270 | ANDRE INDTÆGTER | 1235 | 1280 | 1235-1280 |
Fakturarabat - kunder | 1280 | 1280 | 1280 | ANDRE INDTÆGTER | 1235 | 1280 | 1235-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])))
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.