================================================================================
QUERY 1 — Name this query: OrgHierarchySlicer
Bridge table for the slicer. Layer 1 = blank/null Manager ID. Each SlicerLabel
(e.g. Layer 2 - Bill) expands to Bill plus all direct and indirect reports.
Relationship: this query's [Employee ID] (many) -> your base Headcount [Employee ID] (one).
Slicer field: [SlicerLabel]. Hide [AnchorEmployeeID] from the report if not needed.
Replace Source with your headcount query (e.g. Source = Headcount) when not using sample data; remove any
duplicate type/clean steps if Headcount already applies them.
Copy ONLY the block that starts with "let" and ends with "in OrgHierarchySlicer".
================================================================================

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIip/wkpVidaCUjIBsk5JSZkwMWMIYK+GZmp4IFTKACwYm5YL4pkA3S5QXTYQYXgCgwB7KNwRpycirBIhZQEffEoiKIiCWQZwrRkwsRMTQAckHCIZkwbYaGUCGg1Yl5JRmJSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Manager ID" = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Employee ID", type text}, {"Manager ID", type text}, {"Name", type text}}),

    WithCleanMgr = Table.AddColumn(
        #"Changed Type",
        "Manager ID Clean",
        each
            let
                m = [Manager ID],
                m2 = if m = null or Text.Trim(m) = "" then null else m
            in
                if m2 = [Employee ID] then null else m2,
        type nullable text
    ),
    BaseTable = Table.RenameColumns(
        Table.RemoveColumns(WithCleanMgr, {"Manager ID"}),
        {{"Manager ID Clean", "Manager ID"}}
    ),

    MgrPairs = List.Zip({BaseTable[Employee ID], BaseTable[Manager ID]}),
    MgrMap = Record.FromList(
        List.Transform(MgrPairs, each _{1}),
        List.Transform(MgrPairs, each _{0})
    ),
    GetManager = (empId as text) as nullable text =>
        if empId = null then null else try Record.Field(MgrMap, empId) otherwise null,

    DepthOf = (empId as text) as number =>
        List.Count(
            List.Generate(
                () => [id = empId],
                each [id] <> null,
                each [id = GetManager([id])],
                each 1
            )
        ),

    ByMgr = Table.Group(
        Table.SelectRows(BaseTable, each [Manager ID] <> null),
        {"Manager ID"},
        {{"DirectReports", each [Employee ID], type list}}
    ),
    DirectReportsMap =
        if Table.IsEmpty(ByMgr) then
            Record.FromList({}, {})
        else
            Record.FromList(ByMgr[DirectReports], ByMgr[Manager ID]),

    AllDescendants = (mgrId as text) as list =>
        let
            direct = try Record.Field(DirectReportsMap, mgrId) otherwise {},
            nested = List.Transform(direct, each AllDescendants(_))
        in
            List.Combine({direct, List.Combine(nested)}),

    WithDepth = Table.AddColumn(BaseTable, "LayerDepth", each DepthOf([Employee ID]), Int64.Type),
    WithLabel = Table.AddColumn(
        WithDepth,
        "SlicerLabel",
        each "Layer " & Text.From([LayerDepth]) & " - " & [Name],
        type text
    ),
    WithSpan = Table.AddColumn(
        WithLabel,
        "SpanEmployeeIDs",
        each List.Combine({{[Employee ID]}, AllDescendants([Employee ID])}),
        type list
    ),
    PreExpand = Table.DuplicateColumn(WithSpan, "Employee ID", "AnchorEmployeeID"),
    Expanded = Table.ExpandListColumn(PreExpand, "SpanEmployeeIDs"),
    Renamed = Table.RenameColumns(Expanded, {{"SpanEmployeeIDs", "Employee ID"}}),
    OrgHierarchySlicer = Table.SelectColumns(Renamed, {"SlicerLabel", "AnchorEmployeeID", "Employee ID"})
in
    OrgHierarchySlicer


================================================================================
QUERY 2 — Optional: merge into your EXISTING Headcount query (do not create a second table)
Adds Layer 1 Name, Layer 2 Name, … up to the deepest level in the current refresh.
Open your headcount query → Advanced Editor → append this logic after your last step,
or replace the opening Source/BaseTable chain so BaseTable = your final step name.
Same cleaning rules as Query 1 if you still use the full block from sample Source.
Not required for the span slicer (Query 1 alone filters Bill and all descendants).
Copy ONLY the second block below (from "let" through the final "in").
================================================================================

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIip/wkpVidaCUjIBsk5JSZkwMWMIYK+GZmp4IFTKACwYm5YL4pkA3S5QXTYQYXgCgwB7KNwRpycirBIhZQEffEoiKIiCWQZwrRkwsRMTQAckHCIZkwbYaGUCGg1Yl5JRmJSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Manager ID" = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Employee ID", type text}, {"Manager ID", type text}, {"Name", type text}}),

    WithCleanMgr = Table.AddColumn(
        #"Changed Type",
        "Manager ID Clean",
        each
            let
                m = [Manager ID],
                m2 = if m = null or Text.Trim(m) = "" then null else m
            in
                if m2 = [Employee ID] then null else m2,
        type nullable text
    ),
    BaseTable = Table.RenameColumns(
        Table.RemoveColumns(WithCleanMgr, {"Manager ID"}),
        {{"Manager ID Clean", "Manager ID"}}
    ),

    MgrPairs = List.Zip({BaseTable[Employee ID], BaseTable[Manager ID]}),
    MgrMap = Record.FromList(
        List.Transform(MgrPairs, each _{1}),
        List.Transform(MgrPairs, each _{0})
    ),
    GetManager = (empId as text) as nullable text =>
        if empId = null then null else try Record.Field(MgrMap, empId) otherwise null,

    DepthOf = (empId as text) as number =>
        List.Count(
            List.Generate(
                () => [id = empId],
                each [id] <> null,
                each [id = GetManager([id])],
                each 1
            )
        ),

    NameById = Record.FromList(BaseTable[Name], BaseTable[Employee ID]),

    WalkUpN = (empId as nullable text, n as number) as nullable text =>
        if n <= 0 or empId = null then
            empId
        else
            @WalkUpN(GetManager(empId), n - 1),

    NameAtLayerFromTop = (empId as text, layerFromTop as number) as nullable text =>
        let
            d = DepthOf(empId),
            steps = d - layerFromTop,
            ancestorId = if layerFromTop > d then null else WalkUpN(empId, steps)
        in
            if ancestorId = null then null else try Record.Field(NameById, ancestorId) otherwise null,

    Depths = List.Transform(BaseTable[Employee ID], each DepthOf(_)),
    MaxDepth = if List.IsEmpty(Depths) then 0 else List.Max(Depths),

    HeadcountWithLayerNames = List.Accumulate(
        List.Numbers(1, MaxDepth),
        BaseTable,
        (tbl, n) =>
            Table.AddColumn(
                tbl,
                "Layer " & Text.From(n) & " Name",
                each NameAtLayerFromTop([Employee ID], n),
                type nullable text
            )
    )
in
    HeadcountWithLayerNames