cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mtoenjes Frequent Visitor
Frequent Visitor

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 been a new requirement and I am struggling to find a way to create this solution in PowerBI.

 

Basically, I am connecting to an API and downloading tables of information. One of the tables includes a list of ORGINIZATIONAL GROUPS in the system. The issue is is there are effectively nested tables (but have to make indvidual API calls to get them). Here is my code so far....

 

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)

    in
        response;

 

STEP 1 (orgchart and wirturl): Get the master ORGCHART table and build links, download each link/orgchart table (which includes all Users accociated with that org which is located in userTable).

 

The above code yields the folling table:

2019-05-01 10_54_44-Docebo - Microsoft Visual Studio.png

 

STEP 2: This is the part where I am struggling at to do programatically.

 

For each record/row; IF HASCHILDREN is EQUAL TO "True" then take the ID from the table, build a URL, and pass it into my GetPage funtion. I would also like to loop this as some of these are 3-4 layers deep. 

 

For example, because ID 8 hashchildren, I need to download another table: https://<myapi>.com/manage/v1/orgchart?node_id=8 which returns:2019-05-01 11_13_17-Docebo - Microsoft Visual Studio.png

Once this is returned, I would like to add the IDs to the usersUrl and download these user tables as well. However, I am not sure how to even access these rows as records and use these parameters as variables.

 

 

STEP 3: Basically (from step 1) I would like to end up with a master table that brings all the nested tables forward:

Example

idtitlehaschildrenusersUrluserTable
11DemoFALSEhttps://<myapi>.com/manage/v1/orgchart/11/users[Table]
8EmployeesTRUEhttps://<myapi>.com/manage/v1/orgchart/8/users[Table]
2Salesforce ContactsTRUEhttps://<myapi>.com/manage/v1/orgchart/2/users[Table]
13DISYSFALSEhttps://<myapi>.com/manage/v1/orgchart/13/users[Table]
10NetmetschekFALSEhttps://<myapi>.com/manage/v1/orgchart/10/users[Table]
14NetmetscheckFALSEhttps://<myapi>.com/manage/v1/orgchart/14/users[Table]
9TimeXpertsFALSEhttps://<myapi>.com/manage/v1/orgchart/9/users[Table]

.................and so on...

STEP 4: Once the above is done, I would like a table with ID and TITLE only (from step 3)

STEP 5: THEN take all the users out of the userTables and have one list including only User, User, ID then use the Step 4 table to match the OrgIDs to the name of the Org.

 

Is anyone able to help me out or point me in the right direction for STEP 2?

 

Thank You

1 ACCEPTED SOLUTION

Accepted Solutions
mtoenjes Frequent Visitor
Frequent Visitor

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

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?

2 REPLIES 2
mtoenjes Frequent Visitor
Frequent Visitor

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

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....

2019-05-02 09_48_52-Docebo - Microsoft Visual Studio.png

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?

 

mtoenjes Frequent Visitor
Frequent Visitor

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

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?