REST API Data Source refreshes in Desktop but not in Service

[Grant_Reid]

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]=eyJlSWQwIjoiNzY0MDE2OTUwMDIzM...

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

[ImkeF]

Sorry @Grant_Reid ,
missed your previous message. Glad you've sorted it out.

[Grant_Reid]

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

[ImkeF]

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")),

  #"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 =

    (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 · Accepted solution]

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]

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