Forum Discussion

Powerwoman's avatar
Powerwoman
Helper II
1 year ago
Solved

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   ...
Need Help
  • dufoq3's avatar
    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

     

Powerwoman's avatar
Powerwoman
Helper II
1 year ago

Dear AlienSx ,
you are right! 4820 should be Level 1 as there's only one parent-child relationship!

 

v-saisrao-msft's avatar
v-saisrao-msft
Community Support
1 year ago

Hi Powerwoman 
Thanks for reaching for Microsoft fabric Community 

Based on your query regarding creating a column with hierarchy information, please try the following M-query: 

 
Note: Replace the source path with your original source path 

 
CODE: 

let 

    Source = Excel.Workbook(File.Contents("C:\Users\v-saisrao\Downloads\Hierarchy.xlsx"), null, true), 

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], 

    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), 

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"rowNo", Int64.Type}, {"description", type text}, {"totaling rowNo", type nullable Int64.Type}, {"value", type nullable Int64.Type}}), 

    IsReferenced = (currentRowNo as number) => 

        let 

            ReferencingRows = Table.SelectRows(#"Changed Type", each [totaling rowNo] = currentRowNo) 

        in 

            Table.RowCount(ReferencingRows) > 0, 

    IsReferencedByLevelZero = (currentRowNo as number) => 

        let 

            ReferencingRows = Table.SelectRows(#"Changed Type", each [totaling rowNo] = currentRowNo), 

            Level0Rows = Table.SelectRows(ReferencingRows, each not IsReferenced([rowNo]) and [totaling rowNo] <> null) 

        in 

            Table.RowCount(Level0Rows) > 0, 

    #"Added Level" = Table.AddColumn( 

        #"Changed Type",  

        "Level",  

        each  

            if not IsReferenced([rowNo]) and [totaling rowNo] <> null then 0 

            else if IsReferencedByLevelZero([rowNo]) then 1 

            else if [totaling rowNo] = null and [value] = null then 1 

            else 2, 

        Int64.Type) 

in 

    #"Added Level" 

 

 

If this helps, then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily. 

Hope this works for you! 

Thanks.