Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with looping with parameters on a table and joining similar tables

Hello,   I am hoping someone would be able to help me out or point me in the right direction. I am newer to PowerBI and have written a new connector which is working flawlessly. However, there has ...
  • Anonymous's avatar
    Anonymous
    7 years ago

    For anyone who comes upon this and needs help, I was able to figure out a solution. In the end, I was able to create two seperate tables per requirements. DoceboBranches returns a tables of the branch (org) ID as well as the name. 

     

    Then DoceboUsersWithBranch returns a table of ALL users in the system by downloading users from each branch that was returned from DoceboBranches.

     

    //Special handling to generate the branches table
    DoceboBranches = (orgtable as table) => let //For every row of data that has_children, grab the subbranch table and add it to a new row expand = Table.AddColumn(orgtable, "moreorgs", each if [has_children]=true then Docebo.Feed("https://<myapi>.com/manage/v1/orgchart?node_id=" & [id]) else null), //After we have downloaded the tables, replace the has_children value from true to Expanded to indicate we have already expanded this branch id markExpanded = Table.ReplaceValue(expand, true, "Expanded" , Replacer.ReplaceValue, {"has_children"}) as table, //Converts the moreorgs colum (containing all the newly downloaded tables) into a list and removes all the null values so this will work in the next function withMoreOrgsList = List.RemoveNulls(markExpanded[moreorgs]), //Combine all of the newly downloaded tables in the list into one new table combinenew = Table.Combine(withMoreOrgsList), //Combine the single/newly combined table from above, and attach it to the master result table combined = Table.Combine({markExpanded, combinenew}), //Remove unnecessary columns including moreorgs colum of tables, so we do not accidentally expand these again in the next loop clean = Table.RemoveColumns(combined,{"code","lev","iLeft","iRight","selection_status","selectable","actions","can_manage","icon","color","tooltip", "moreorgs"}), //Runs a logical test to check if we have more child items we need to iterate through by checking for true values in the has_children column moredata = List.Contains(clean[has_children],true), //If more data is true then we will pass the result table through the DoceboBranches function again until we don't have any more children result = if moredata = true then DoceboBranches(clean) else clean in result; //Special handling to generate the users table DoceboUsersWithBranch = let //The custom build branches table is required to build this user table. Therefore, we request it here branchtable = DoceboBranches(Docebo.Feed(OrgChart)), //Removes extra/unneeded columns from the table clean = Table.RemoveColumns(branchtable,{"title","has_children"}), //Build URLs so that we can request all users within an branch addbranchusers = Table.AddColumn(clean, "usersUrl", each "https://<myapi>.com/manage/v1/orgchart/"&[id]&"/users", type text), //Take the URL and add the response (table) to a new colum called "userTable" response = Table.AddColumn(addbranchusers, "userTable", each Docebo.Feed([usersUrl]), type table), //Expand each table (containing all users for each branch) expand = Table.ExpandTableColumn(response, "userTable", {"user_id", "username", "first_name", "last_name", "email", "last_access_date", "register_date"}, {"user_id", "username", "first_name", "last_name", "email", "last_access_date", "register_date"}), //Remove the no longer needed usersUrl column to get our result result = Table.RemoveColumns(expand,{"usersUrl"}) in result;

    If anyone has any suggestions on how to simplify this code, please let me know.

     

    Also, in "DoceboUsersWithBranch" I have to call the "DoceboBranches" function again. Therefore DoceboBranches actually runs twice to return the table twice. Does anyone know how to change this so that DoceboBranches is cached the first time it is run, with the ability to use it in another function?