Forum Discussion

kmpmuthu's avatar
kmpmuthu
Frequent Visitor
4 years ago

How to transfer SQL Hierarchy data type column

We are using Company master table which is having "Hierarchy" data type column. This column having hierarchical column to parent child relationship. We want to use this hierarchy column in slicer [us...
Need Help
kmpmuthu's avatar
kmpmuthu
Frequent Visitor
4 years ago

Adding more info,

 

In table "Hierarchy" column itself has hierarchical data. Like this column has Parent and child data. If we are trying to use "Hierarchy Slicer" ,its expecting different column to form parent child data structure.

Hierarchy data type column has 5 level of parent child hierarchical data.

Parent 1

   Child 1

        child 1-1

        child 1-2

    Child 2

    Child 3

    Child 4

         child 4-1

         child 4-2

        child 4-3

        child 4-4

Do we have any visualization can handle same column data for both parent and child hierarchy.

 

Thanks in Advance

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago

First of all, you should import or compile from HR or whatever system a fundamental table,

let
    RecursiveHierarchy = (_supID as text, _hierarchy as list) as list => let pos = List.PositionOf(Cols{0}, _supID) in if pos<>-1 then @RecursiveHierarchy(Cols{1}{pos}, {_supID} & _hierarchy) else _hierarchy,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcwxDgAgCAPAvzC7WHiGcXMi/v8bKmLArRdaVKlSoT5ao1k2jqpHyw9IQJQ4ReuzAx/uCY/4yZkSH8Vm4oBjLg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Supervisor ID" = _t]),
    Cols = Table.ToColumns(Source),
    #"Added Hierarchy" = Table.AddColumn(Source, "Hierarchy", each Text.Combine(RecursiveHierarchy([#"Supervisor ID"], {}) & {[ID]}, "|")),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.SelectColumns(#"Added Hierarchy", "Hierarchy"), "Hierarchy", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Hierarchy.1", "Hierarchy.2", "Hierarchy.3", "Hierarchy.4"})
in
    #"Split Column by Delimiter"

  • kmpmuthu's avatar
    kmpmuthu
    Frequent Visitor
    4 years ago

    That's great news. Thanks for your detailed explaination. 

    To display to user, can we show different column (another column) instead of hierarchy id's.

     

     

     

    Appreciate if you could provide any solution for this. Thanks in Advance.