Forum Discussion
Help with looping with parameters on a table and joining similar tables
- Anonymous7 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?
I got 1 Step further with this problem by using Table.AddColum and each if
DoceboBranches = (url as text) as table =>
let
orgchart = GetPage("https://<myapi>.com/manage/v1/orgchart"),
withurl = Table.AddColumn(orgchart, "usersUrl", each "https://<myapi>.com/manage/v1/orgchart/"&[id]&"/users", type text),
response = Table.AddColumn(withurl, "userTable", each Docebo.Feed([usersUrl]), type table),
moreorgs = Table.AddColumn(response, "moreOrgTable", each if [has_children]=true then GetPage("https://<myapi>.com/manage/v1/orgchart?node_id=" & [id]) else null)
in
moreorgs;Now I get this....
Inside the moreOrgTable, is a table that looks almost idential, but with different IDs and titles.
However I am not sure
(1) How to expand/transform this moreOrgTable
(2) Loop through my function again to append more "moreOrgTables" as more are available within those "moreOrgTables".
Can ayone assist?
- Anonymous7 years agoNot applicable
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?