Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 1.  Automated Organization Chart

    Top Contributor
    Posted May 24, 2022 11:13 PM
    Hopefully someone here can help me. I have been asked to create an automated org chart via active directory as my source system.

    I have power automate script thay loads all the required data into SQL for me to use in power bi.

    The problem I'm facing is trying to use the maq org chart visual, it requires the Id numbers to be in decending order. So ceo is #1 and their direct reports start at 2 and go from there. Then their direct reports are numbered greater than them and so on. The problem I'm stuck on is how do I create this hierarchy in the correct order, so that a manager is always a lower number than those who report to them. 

    I'm thinking I have to loop through every employee with power automate and get direct reports and assign an auto incrementing variable. Please tell me there's a better option 



  • 2.  RE: Automated Organization Chart

    Posted May 25, 2022 08:15 PM
    I would be happy to write an SP (SQL Stored Procedure) that would run through the top-down hierarchy and number the levels/persons for you on you SQL table.  This SP then could be called from your PS as a single call instead of doing a looping PS.

    ------------------------------
    Bob Stahr
    IT Consultant
    Bob Stahr-1
    WA
    ------------------------------

    Original Message


  • 3.  RE: Automated Organization Chart

    Bronze Contributor
    Posted May 30, 2022 03:04 PM
    Edited by Amon Seagull May 30, 2022 03:05 PM
    Sam, I may be oversimplfying, or perhaps this approach is not robust, but can you use a Group By to collect the direct reports in a comma-delimited string, then split by delimiter into rows (if it's reliable that the order is maintained) to spit them out? If that's reliable, then the Add Index will finish it off.

    If I have a table Emps with two columns Manager and Employee, I can use this M code (mostly generated through the UI):
    let
      Source = Emps,
      #"Replaced Value" = Table.ReplaceValue(Source,"","TOP",Replacer.ReplaceValue,{"Manager"}), // my CEO had a blank for the "manager"
      #"Grouped Rows" = Table.Group(#"Replaced Value", {"Manager"}, {{"Directs", each Text.Combine([Employee], ","), type nullable text}}),
      #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Grouped Rows", {{"Directs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Directs"),
      #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Directs", type text}}),
      #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
    in
      #"Added Index"

    If this helps you, great! But I don't claim to know exactly what the maq visual requires as input. As they used to say, ymmv.

    ------------------------------
    Amon Seagull
    McKinney
    ------------------------------

    Original Message


  • 4.  RE: Automated Organization Chart

    Top Contributor
    Posted May 31, 2022 08:26 AM
    @Amon Seagull that's not a terrible idea, it could help grouping i suppose. I figured out the issue I was having was i needed a "root" person for every department, and that person needed to be person #1. Everyone else seemingly falls into place correctly after that. The challenge then became how to ID the department head. There is no consistency in job title or position (manager, avp, vp etc...) But every department in my organization does report back to someone in the executive level, so within some finagling in the SQL code I was able to map the executive team into their subordinate departments and assign them a employee ID of 1 which allowed them to then be the root person for that department.

    I also used a different org chart visual instead of the MAQ because the formatting options on the MAQ wouldn't allow me to adjust the text to fit in the bubbles.

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------

    Original Message