Forum Discussion

Grant_Reid's avatar
Grant_Reid
Frequent Visitor
3 years ago
Solved

REST API Data Source refreshes in Desktop but not in Service

I’ve been tasked to roll out a solution provided by a third party. Everything works perfectly in Desktop and the expected number of records are returned. However when published to the Service I get the following error;

“ This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed”

 

What had initial seemed an easy task now has become a nightmare. This is my first foray with REST API’s, and I’ve been muddling for days through this without any success, with a looming deadline. I fear that if I continue to muddle, I won’t meet the deadline.

 

My code is as follows;

 

 

let
  // Build the URL for the API call
  #"Header" = Binary.ToText(Text.ToBinary(#"User Email" & ":" & #"API Token")),
  startUrl = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info?page[first]=1000",

  /*  Description: Fetches one page of data
        url - URL to fetch data
      Output Parameter:
        ret - record consisting of two elements
        retData - data for current page
        retNext - URL for next page
  */
  getOnePage =
    (url) as record =>
    let
        // Get the specified URL and parse the JSON response
        devicePage = Json.Document(
          Web.Contents(
            url,
            [Headers=[
              #"Authorization" = "Basic " & #"Header",
              #"content-type" = "application/json"
              ]
            ]
          )
        ),
        
        // Extract the data and links fields and return it
        deviceData = try devicePage[data] otherwise null,
        next = try devicePage[links][next] otherwise null,
        ret = [retData=deviceData, retNext=next]
    in
        ret,

  // Fetch each page until there are no more pages by following the next link. Append subsequent pages.  
  deviceList = List.Generate(
    // Fetch the first page
    ()=> [ret = getOnePage(startUrl)],
    // Stop when there is no more data
    each [ret][retData]<>null,
    // Get the next page using the next link
    each [ret = getOnePage([ret][retNext])],
    // Return only the data
    each [ret][retData]),
    
  // Convert the list into a table
  deviceListTable = Table.FromList(deviceList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  // Expand the first column into a separate row for each list item
  deviceTable = Table.ExpandListColumn(deviceListTable, "Column1"),
    // Expand the API response into a column for each first level field
  #"Expanded Column1" = Table.ExpandRecordColumn(deviceTable, "Column1", {"id", "attributes", "relationships"}, {"Unique ID of the Device", "Column1.attributes", "Column1.relationships"}),
  // Expand each field in the attributes column into a separate column
  #"Expanded Column1.attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attributes", {"ipAddresses", "deviceName", "deviceType", "makeModel", "vendorName", "softwareVersion", "serialNumber", "description", "firmwareVersion", "lastModified", "lastSeenTime", "onlineStatus"}, {"LAN IP Address(es)", "Device Name", "Device Type", "Device Model", "Device Vendor", "Device Software Version", "Device Serial Number", "Device Description", "Device Firmware Version", "Last Time Device Attributes Were Modified", "Last Time the Device Was Seen", "Device Status"}),

  // Convert the IP addresses list into a single string
  #"Extracted Values" = Table.TransformColumns(#"Expanded Column1.attributes", {"LAN IP Address(es)", each Text.Combine(List.Transform(_, Text.From), ","), type text}),

  // Expand the data field in the relationships column
  #"Expanded Column1.relationships" = Table.ExpandRecordColumn(#"Extracted Values", "Column1.relationships", {"tenant"}, {"Column1.relationships.tenant"}),
  // Expand the data field in the relationships.tenant column
  #"Expanded Column1.relationships.tenant" = Table.ExpandRecordColumn(#"Expanded Column1.relationships", "Column1.relationships.tenant", {"data"}, {"Column1.relationships.tenant.data"}),
  // Expand the relationships.tenant.data column into separate columns for each field
  #"Expanded Column1.relationships.tenant.data" = Table.ExpandRecordColumn(#"Expanded Column1.relationships.tenant", "Column1.relationships.tenant.data", {"id", "attributes"}, {"Site Unique ID", "Column1.relationships.tenant.data.attributes"}),
  // Expand the domainPrefix field in the relationships.tenant.data.attributes column
  #"Expanded Column1.relationships.tenant.data.attributes" = Table.ExpandRecordColumn(#"Expanded Column1.relationships.tenant.data", "Column1.relationships.tenant.data.attributes", {"domainPrefix"}, {"Domain Prefix"}),
 
  // Creating a new column that generates distinct names by combining the Device Name and the IP addresses of each device
  formattedDeviceInventoryTable = Table.AddColumn(#"Expanded Column1.relationships.tenant.data.attributes", "Unique Device Name (DeviceName & LAN IP(s)", each [Device Name] &"@"& [#"LAN IP Address(es)"])
in
    formattedDeviceInventoryTable

 

 

 

As I mentioned above, this works perfectly in Desktop, but I get the “dynamic data source error” after publishing to the Service.

If I understand the above code correctly, and I think I do, it initially uses the URL https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info?page[first]=1000 to get the next page link to generate the list.

Not sure if this is relevant, but the next page URL takes the form of;

https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info?page[after]=eyJlSWQwIjoiNzY0MDE2OTUwMDIzMzk0MzA3IiwidElkMCI6IjcxMzI3Nzg0MjI1NjIwNTU2NSJ9&page[first]=1000&tenants=543336304149464829

So different to initial URL passed e.g. query parameters passed and the order they are passed

I’m pulling my hair out here so hope that someone can assist.

 

The code I have at the moment is;

 

 

/* Description: Fetches the device inventory for the sites that the user is authorized on.
  Input Parameter:
   Request for inventory list
  Output Parameter:
    Device Inventory - Table of devices data for the sites that the user is authorized on.
*/

let
  // Build the URL for the API call
  #"Header" = Binary.ToText(Text.ToBinary(#"User Email" & ":" & #"API Token")),
  #"BaseURL" = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info",
  #"Query" = "page[first]=1000",
  //startUrl = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info?page[first]=50",
  startUrl = #"BaseURL" & #"Query",

  /*  Description: Fetches one page of data
        url - URL to fetch data
      Output Parameter:
        ret - record consisting of two elements
        retData - data for current page
        retNext - URL for next page
  */
  getOnePage =
    (url) as record =>
    let
        // Get the specified URL and parse the JSON response
        devicePage = Json.Document(
        Web.Contents(
          #"BaseURL",
           [
             Query = #"Query",
             Headers=[
               #"Authorization" = "Basic " & #"Header",
               #"content-type" = "application/json"
               ]
            ]
        )
    ),
        
       
        // Extract the data and links fields and return it
        deviceData = try devicePage[data] otherwise null,
        next = try devicePage[links][next] otherwise null,
        ret = [retData=deviceData, retNext=next]
    in
        ret,

  // Fetch each page until there are no more pages by following the next link. Append subsequent pages.  
  deviceList = List.Generate(
    // Fetch the first page
    ()=> [ret = getOnePage(#"BaseURL")],
    // Stop when there is no more data
    each [ret][retData]<>null,
    // Get the next page using the next link
    each [ret = getOnePage([ret][retNext])],
    // Return only the data
    each [ret][retData]),
    
  // Convert the list into a table
  deviceListTable = Table.FromList(deviceList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  // Expand the first column into a separate row for each list item
  deviceTable = Table.ExpandListColumn(deviceListTable, "Column1"),
  
  // Expand the API response into a column for each first level field
  #"Expanded Column1" = Table.ExpandRecordColumn(deviceTable, "Column1", {"id", "attributes", "relationships"}, {"Unique ID of the Device", "Column1.attributes", "Column1.relationships"}),
  // Expand each field in the attributes column into a separate column
  #"Expanded Column1.attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attributes", {"ipAddresses", "deviceName", "deviceType", "makeModel", "vendorName", "softwareVersion", "serialNumber", "description", "firmwareVersion", "lastModified", "lastSeenTime", "onlineStatus"}, {"LAN IP Address(es)", "Device Name", "Device Type", "Device Model", "Device Vendor", "Device Software Version", "Device Serial Number", "Device Description", "Device Firmware Version", "Last Time Device Attributes Were Modified", "Last Time the Device Was Seen", "Device Status"}),

  // Convert the IP addresses list into a single string
  #"Extracted Values" = Table.TransformColumns(#"Expanded Column1.attributes", {"LAN IP Address(es)", each Text.Combine(List.Transform(_, Text.From), ","), type text}),

  // Expand the data field in the relationships column
  #"Expanded Column1.relationships" = Table.ExpandRecordColumn(#"Extracted Values", "Column1.relationships", {"tenant"}, {"Column1.relationships.tenant"}),
  // Expand the data field in the relationships.tenant column
  #"Expanded Column1.relationships.tenant" = Table.ExpandRecordColumn(#"Expanded Column1.relationships", "Column1.relationships.tenant", {"data"}, {"Column1.relationships.tenant.data"}),
  // Expand the relationships.tenant.data column into separate columns for each field
  #"Expanded Column1.relationships.tenant.data" = Table.ExpandRecordColumn(#"Expanded Column1.relationships.tenant", "Column1.relationships.tenant.data", {"id", "attributes"}, {"Site Unique ID", "Column1.relationships.tenant.data.attributes"}),
  // Expand the domainPrefix field in the relationships.tenant.data.attributes column
  #"Expanded Column1.relationships.tenant.data.attributes" = Table.ExpandRecordColumn(#"Expanded Column1.relationships.tenant.data", "Column1.relationships.tenant.data.attributes", {"domainPrefix"}, {"Domain Prefix"}),
 
  // Creating a new column that generates distinct names by combining the Device Name and the IP addresses of each device
  formattedDeviceInventoryTable = Table.AddColumn(#"Expanded Column1.relationships.tenant.data.attributes", "Unique Device Name (DeviceName & LAN IP(s)", each [Device Name] &"@"& [#"LAN IP Address(es)"])
in
    formattedDeviceInventoryTable

 

 

It would seem that this is falling over in applied step "deviceList" where I'm only expecting one record but have in excess of 1000 records returned.

 

PS: I have posted this in another forum and have been pointed to Chris Webb's Blog, specific topics "Web.Contents(), M Functions And Dataset Refresh Errors In Power BI" & "Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code" but have been unable to reverse engineer / integrate into what I need. I think the end result might be an endless loop 😞

 

Kind Regards - Grant

Need Help
  • Grant_Reid's avatar
    Grant_Reid
    3 years ago

    Hi All

     

    Many thanks Imke for helping me resolve this. Working code below.

    let
    // Build the URL for the API call
    #"Header" = Binary.ToText(Text.ToBinary(#"User Email" & ":" & #"API Token")), 
    mainURL = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info", 
    startQuery = [#"page[first]" = "50"], 
    /*  Description: Fetches one page of data
        url - URL to fetch data
      Output Parameter:
        ret - record consisting of two elements
        retData - data for current page
        retNext - URL for next page
  */
    getOnePage = (query) as record =>
        let
            // Get the specified URL and parse the JSON response
            devicePage = Json.Document(
                Web.Contents(
                    mainURL, 
                    [
                        Query = query, 
                        Headers = [
                            #"Authorization" = "Basic " & #"Header", 
                            #"content-type"  = "application/json"
                        ]
                    ]
                )
            ), 
            // Extract the data and links fields and return it
            deviceData = try devicePage[data] otherwise null, 
            next = try devicePage[links][next] otherwise null, 
            ret = [retData = deviceData, retNext = next]
        in
            ret, 
    // Fetch each page until there are no more pages by following the next link. Append subsequent pages.  
    deviceList = List.Generate(
        // Fetch the first page
        () => [ret = getOnePage(startQuery)], 
        // Stop when there is no more data
        each try [ret][retData] <> null otherwise false, 
        // Get the next page using the next link - numbers in the query parameters must be formatted as text, hence the gymnastics
        each [
            ret = getOnePage(
                Record.FromTable(
                    Table.TransformColumnTypes(
                        Record.ToTable(Uri.Parts([ret][retNext])[Query]), 
                        {{"Value", type text}}
                    )
                )
            )
        ], 
        // Return only the data
        each [ret][retData]
    ),
    Custom1 = deviceList
in
    Custom1

     

5 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi Grant_Reid ,
    looks like you are on the right track. Try the changes below.
    Your solution didn't use the parameter in the function and therefore the same query has been executed again and again in the List.Generate operation.


    /* Description: Fetches the device inventory for the sites that the user is authorized on.
      Input Parameter:
       Request for inventory list
      Output Parameter:
        Device Inventory - Table of devices data for the sites that the user is authorized on.
    */

    let
      // Build the URL for the API call
      #"Header" = Binary.ToText(Text.ToBinary(#"User Email" & ":" & #"API Token")),
      #"Query" = "page[first]=1000",
     // startUrl = #"BaseURL" & #"Query", 

      /*  Description: Fetches one page of data
            url - URL to fetch data
          Output Parameter:
            ret - record consisting of two elements
            retData - data for current page
            retNext - URL for next page
      */
      getOnePage =
        (query) as record =>
        let
            // Get the specified URL and parse the JSON response
            devicePage = Json.Document(
            Web.Contents(
              #"BaseURL",
               [
                 Query = query,
                 Headers=[
                   #"Authorization" = "Basic " & #"Header",
                   #"content-type" = "application/json"
                   ]
                ]
            )
        ),
           
           
            // Extract the data and links fields and return it
            deviceData = try devicePage[data] otherwise null,
            next = try devicePage[links][next] otherwise null,
            ret = [retData=deviceData, retNext=next]
        in
            ret,

      // Fetch each page until there are no more pages by following the next link. Append subsequent pages.  
      deviceList = List.Generate(
        // Fetch the first page
        ()=> [ret = getOnePage("page[first]=1000")],
        // Stop when there is no more data
        each [ret][retData]<>null,
        // Get the next page using the next link
        each [ret = getOnePage([ret][retNext])], // maybe you have to use "page[after]=" & [ret][retNext] as function arguments instead
        // Return only the data
        each [ret][retData]),
       
      // Convert the list into a table
      deviceListTable = Table.FromList(deviceList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      // Expand the first column into a separate row for each list item
      deviceTable = Table.ExpandListColumn(deviceListTable, "Column1"),
     
      // Expand the API response into a column for each first level field
      #"Expanded Column1" = Table.ExpandRecordColumn(deviceTable, "Column1", {"id", "attributes", "relationships"}, {"Unique ID of the Device", "Column1.attributes", "Column1.relationships"}),
      // Expand each field in the attributes column into a separate column
      #"Expanded Column1.attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attributes", {"ipAddresses", "deviceName", "deviceType", "makeModel", "vendorName", "softwareVersion", "serialNumber", "description", "firmwareVersion", "lastModified", "lastSeenTime", "onlineStatus"}, {"LAN IP Address(es)", "Device Name", "Device Type", "Device Model", "Device Vendor", "Device Software Version", "Device Serial Number", "Device Description", "Device Firmware Version", "Last Time Device Attributes Were Modified", "Last Time the Device Was Seen", "Device Status"}),

      // Convert the IP addresses list into a single string
      #"Extracted Values" = Table.TransformColumns(#"Expanded Column1.attributes", {"LAN IP Address(es)", each Text.Combine(List.Transform(_, Text.From), ","), type text}),

      // Expand the data field in the relationships column
      #"Expanded Column1.relationships" = Table.ExpandRecordColumn(#"Extracted Values", "Column1.relationships", {"tenant"}, {"Column1.relationships.tenant"}),
      // Expand the data field in the relationships.tenant column
      #"Expanded Column1.relationships.tenant" = Table.ExpandRecordColumn(#"Expanded Column1.relationships", "Column1.relationships.tenant", {"data"}, {"Column1.relationships.tenant.data"}),
      // Expand the relationships.tenant.data column into separate columns for each field
      #"Expanded Column1.relationships.tenant.data" = Table.ExpandRecordColumn(#"Expanded Column1.relationships.tenant", "Column1.relationships.tenant.data", {"id", "attributes"}, {"Site Unique ID", "Column1.relationships.tenant.data.attributes"}),
      // Expand the domainPrefix field in the relationships.tenant.data.attributes column
      #"Expanded Column1.relationships.tenant.data.attributes" = Table.ExpandRecordColumn(#"Expanded Column1.relationships.tenant.data", "Column1.relationships.tenant.data.attributes", {"domainPrefix"}, {"Domain Prefix"}),
     
      // Creating a new column that generates distinct names by combining the Device Name and the IP addresses of each device
      formattedDeviceInventoryTable = Table.AddColumn(#"Expanded Column1.relationships.tenant.data.attributes", "Unique Device Name (DeviceName & LAN IP(s)", each [Device Name] &"@"& [#"LAN IP Address(es)"])
    in
        formattedDeviceInventoryTable
    • Grant_Reid's avatar
      Grant_Reid
      Frequent Visitor
      3 years ago

      Hi All

       

      Many thanks Imke for helping me resolve this. Working code below.

      let
    // Build the URL for the API call
    #"Header" = Binary.ToText(Text.ToBinary(#"User Email" & ":" & #"API Token")), 
    mainURL = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info", 
    startQuery = [#"page[first]" = "50"], 
    /*  Description: Fetches one page of data
        url - URL to fetch data
      Output Parameter:
        ret - record consisting of two elements
        retData - data for current page
        retNext - URL for next page
  */
    getOnePage = (query) as record =>
        let
            // Get the specified URL and parse the JSON response
            devicePage = Json.Document(
                Web.Contents(
                    mainURL, 
                    [
                        Query = query, 
                        Headers = [
                            #"Authorization" = "Basic " & #"Header", 
                            #"content-type"  = "application/json"
                        ]
                    ]
                )
            ), 
            // Extract the data and links fields and return it
            deviceData = try devicePage[data] otherwise null, 
            next = try devicePage[links][next] otherwise null, 
            ret = [retData = deviceData, retNext = next]
        in
            ret, 
    // Fetch each page until there are no more pages by following the next link. Append subsequent pages.  
    deviceList = List.Generate(
        // Fetch the first page
        () => [ret = getOnePage(startQuery)], 
        // Stop when there is no more data
        each try [ret][retData] <> null otherwise false, 
        // Get the next page using the next link - numbers in the query parameters must be formatted as text, hence the gymnastics
        each [
            ret = getOnePage(
                Record.FromTable(
                    Table.TransformColumnTypes(
                        Record.ToTable(Uri.Parts([ret][retNext])[Query]), 
                        {{"Value", type text}}
                    )
                )
            )
        ], 
        // Return only the data
        each [ret][retData]
    ),
    Custom1 = deviceList
in
    Custom1

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi,

         

        Thanks for providing the working code, it's helped me out already. Sadly I've been pulling out my hairs on this additional error I'm getting and can't seem to get rid of...

         

        When applying the same corrections and trying to refresh in Power BI Online, I get the following error message: The 'Column1' column does not exist in the rowset. Table: Device Inventory.

         

        When trying to debug this, it seems like the refresh starts failing from the following step onwards:

        // Convert the list into a table
  deviceListTable = Table.FromList(deviceList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

         

        This is the exact step where your code ends: it renames deviceList to Custom1 and that's it. This gives me the following result in Power BI Desktop:

         

        The first next step is the one above, where a list is converted into a table, and Power BI Desktop automatically names the only column in this new table 'Column1'.

         

        It's this 'Column1' the Power BI Online refresh eventually fails upon.

         

        However, for the table to give the necessary fields and data, this step and quite some other additional steps are needed, as specified in your original post.

        Does that mean you haven't applied any subsequent steps to the query after defining the Custom1 step? How did this eventually work for you in the end?

        Is there anyone who encountered the same problem already? 

         

        Thanks for all feedback!

         

        Kind regards 🙂

         

        nsfe

  • Grant_Reid's avatar
    Grant_Reid
    Frequent Visitor
    3 years ago

    Hi Imke

    Thank you very much for your prompt response.

    I plugged in your modified code as I received it. Unfortunately I did not get the expected result. There still seems to be an issue with the List.Generate operation.

    I tried your suggestion of using "page[after]=" & [ret][retNext] as function arguments. Not sure I put this in the correct place though - see code below.

     

    /* Description: Fetches the device inventory for the sites that the user is authorized on.
  Input Parameter:
   Request for inventory list
  Output Parameter:
    Device Inventory - Table of devices data for the sites that the user is authorized on.
*/

let
  // Build the URL for the API call
  #"Header" = Binary.ToText(Text.ToBinary(#"User Email" & ":" & #"API Token")),
  #"BaseURL" = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info",
  #"Query" = "page[first]=1000",
  //startUrl = "https://auvikapi.eu1.my.auvik.com/v1/inventory/device/info?page[first]=1000",
 // startUrl = #"BaseURL" & #"Query", 

  /*  Description: Fetches one page of data
        url - URL to fetch data
      Output Parameter:
        ret - record consisting of two elements
        retData - data for current page
        retNext - URL for next page
  */
  getOnePage =
    (query) as record =>
    let
        // Get the specified URL and parse the JSON response
        devicePage = Json.Document(
        Web.Contents(
          #"BaseURL",
           [
             Query = query,
             Headers=[
               #"Authorization" = "Basic " & #"Header",
               #"content-type" = "application/json"
               ]
            ]
        )
    ),
       
       
        // Extract the data and links fields and return it
        deviceData = try devicePage[data] otherwise null,
        next = try devicePage[links][next] otherwise null,
        ret = [retData=deviceData, retNext=next]
    in
        ret,

  // Fetch each page until there are no more pages by following the next link. Append subsequent pages.  
  deviceList = List.Generate(
    // Fetch the first page
    ()=> [ret = getOnePage("page[first]=1000")],
    // Stop when there is no more data
    each [ret][retData]<>null,
    // Get the next page using the next link
    //each [ret = getOnePage([ret][retNext])], // maybe you have to use "page[after]=" & [ret][retNext] as function arguments instead
    each [ret = getOnePage("page[after]=" & [ret][retNext])],
    // Return only the data
    each [ret][retData])
in
    deviceList

     

    Hope you can help. Am at my wits end with this. Probably in over my head.

    Kind Regards - Grant