Forum Discussion

DaveBosman's avatar
DaveBosman
Regular Visitor
4 years ago
Solved

Creating new table from rest calls based on information in other table

Hi, 

I'm trying to get all users and the groups that they are added to from 1 of our tools

I already managed to get the users with following

let 
Source = Json.Document(Web.Contents(JIRA_URL & "/rest/api/2/user/search?query=" & USERQUERY)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"self", "accountId", "displayName", "active", "locale"}, {"Column1.self", "Column1.accountId", "Column1.displayName", "Column1.active", "Column1.locale"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Custom", each [Column1.self] &"&expand=groups"),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "restcall"}})
in
    #"Renamed Columns"

However to get all information about the user, including the groups i need to launch a new rest call per row in the table i already have. (I created a column with the rest call i need to perform.)

However i'm unable to figure out how to create the new table and populate it with the information from these calls.

 

All help would be greatly appreciated. 

 

Regards

Dave

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    Hi DaveBosman 

    Download sample PBIX file

    You can use List.Transform to call the REST API again for each user in the column you created from your first REST call. 

    Without your actual data I can only provide an example like this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstL1aK1YlWKi7ILyoBs0qKEstSc8DMtNKS0qJUMDO5NAfCjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [URL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"URL", type text}}),

    GetURL = (URL) =>
        let Result = Web.BrowserContents("https://www.bbc.co.uk/"&URL)
        in  Result,

    Pages       = List.Transform(#"Changed Type"[URL], each GetURL(_)),
    #"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

    List.Transform calls GetURL for each value in the #'Changed Type"[URL] column

    Regards

     

    Phil

1 Reply

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    4 years ago

    Hi DaveBosman 

    Download sample PBIX file

    You can use List.Transform to call the REST API again for each user in the column you created from your first REST call. 

    Without your actual data I can only provide an example like this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstL1aK1YlWKi7ILyoBs0qKEstSc8DMtNKS0qJUMDO5NAfCjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [URL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"URL", type text}}),

    GetURL = (URL) =>
        let Result = Web.BrowserContents("https://www.bbc.co.uk/"&URL)
        in  Result,

    Pages       = List.Transform(#"Changed Type"[URL], each GetURL(_)),
    #"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

    List.Transform calls GetURL for each value in the #'Changed Type"[URL] column

    Regards

     

    Phil