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

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"