Create a column with hierarchy information

Hi there,

I have a table:

rowNo	description	totaling rowNo	value
1009	Level 0	1030
1009	Level 0	4820
1030	Level 1	1050
1030	Level 1	1060
1050	data 1		10
1060	data 2		20
4820	data 3		50

I need a column that gives me the level of a row.

Example:
rowNo 1009 totals all rows with rowNo 1030 and 4820. rowNo 1009 is the highest level as it's not included in totalling rowNo

Please note: 4820 should be Level 1, not 2.

Is there a possibility to do this dynamically? Would love to do it in Power Query, not in Dax

Thank you for your help!

dufoq3

1 year ago

Hi Powerwoman, another approach:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwsFTSUfJJLUvNUTAAsgwNjEGUUqwOFkkTCyMkSbBCiKQhWKcpPkkzJEmwwpTEkkSwHFgaKmMGlzGCyBhBZKA2g2WMITJAQ2JjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [rowNo = _t, description = _t, #"totaling rowNo" = _t, value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"rowNo", type text}, {"totaling rowNo", type text}, {"value", Int64.Type}}),
    R = Function.Invoke(Record.FromList, Table.ToColumns(Table.SelectRows(ChangedType, each not List.Contains({null, ""}, [totaling rowNo]))[[rowNo], [totaling rowNo]])),
    F = (child, optional lvl)=>
        let l = lvl ?? 0,
            a = Record.FieldOrDefault(R, child, 0), // 0 = highest parent (Level 0)
            b = if a = 0 then l else @F(a, l+1)
        in  b,
    Ad_Level = Table.AddColumn(ChangedType, "Level", each F([rowNo]), type text)
in
    Ad_Level

Forum Discussion

Create a column with hierarchy information