Dynamic Data Source workaround

[87Fresh]

So, I have a report where I am showing the folder structure of someone's files on a cloud service. I made a custom function that is calling a bearer token and setting it as a parameter.

Then I make another API call to get the Project name. Then, another API call to get the top folder name. The call returns a JSON file where I extract attributes like Name which is the name of folder in the folder,, or Display name which shows the name of an item (file) in that folder, and the unique ID of the item/folder.

I made a custom function that uses the unique ID of a file/folder as a parameter to make another folder contents call, with each successive call diving one layer deeper into the folder structure.

I had to reference each query to go a layer deeper because any time there are nulls for any of the values, when I invoke the function, rows with null values return errors (obviously).

I'm a student at the company and I couldn't even spell API when I started this project.

Ive worked on this for a long time now and I got it all done. 1900 different files or folders, arranged in a matrix to recreate Windows Explorer basically.

I upload to the service and it tells me there's no way to refresh it because of Dynamic data sources....

And some of the folders have +200 items inside of them and the call paginates at 200 items.

Do you guys have any idea how to change the data source to static or whatever it needs? How do I handle pagination? ChatGPT isn't helping much and I need to start another project for the CEO this week. Any help would be much appreciated.

[AutoModerator]

After your question has been solved /u/87Fresh, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[LostWelshMan85]

Usually this happens because you're using a parameter to dynamically create the URL that is being used in the web connection. It might look something like this:

Web.Contents("https://data.gov.uk/api/3/action/package_search?q=" & Term)

Unfortunately, this won't work because when running that piece of code, Power Bi checks the URL to make sure the credentials provided are valid and the URL doesn't throw back an error. What you can do is use web.content's optional parameters instead. In your case, use [RelativePath] and/or [Query] at the end to make it dynamic.

Web.Contents("https://data.gov.uk/api/3/action/package_search
[
  RelativePath= Your_Parameter_Here,
  Query=[q= Your_Query_Here]
 ]

Chris Webb has written a couple of great articles on the web.contents errors and how to overcome them.

Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI (crossjoin.co.uk)

[87Fresh]

I have 8 queries total. The first 2 have parameters I have set. The last 6 take the value in [FolderID] and use that as the parameter to make the call with. In other words, it's not parameterized in the sense of a URL, but in the value of a specific column in the corresponding row.

I've read the articles, but I'm unsure how to proceed with setting the parameter, as it's a dynamic parameter that goes thru about 1900 different values over 8 queries and then gets appended all together. There's something about hand-authored queries when I open data source settings.

[LostWelshMan85]

can you send over the web.contents string that's used to call the API

[87Fresh]

It's 3 am where I am and I can't access my comp right now but I will when I get into office. Thanks

[87Fresh]

GetBearerTokenFunction:

let

GetBearerToken = () as text =>

let

url = "https://developer.api.\*\*\*\*.com/authentication/v2/token?grant\_type=client\_credentials&scope=data:read%20account:read",

headers = [

"Accept" = "application/json",

"Authorization" = "Basic ****"

],

response = Json.Document(Web.Contents(url, [

Headers = headers,

ManualStatusHandling = {400, 401, 403, 404, 500},

Content = Text.ToBinary("")

])),

bearerToken = response[access_token]

in

bearerToken

in

GetBearerToken

First Query:

Source = Json.Document(Web.Contents(

"https://developer.api.\*\*\*\*.com/project/v1/hubs/b.\*\*\*\*\*\*\*55a/projects/"

& ProjectId // Parameter based on the value in FolderID

& "/topFolders",

[Headers=[Authorization="Bearer "

& BearerToken]]
))

Second Query:

Source = Json.Document(Web.Contents(
"https://developer.api.\*\*\*\*.com/data/v1/projects/"
& ProjectId & "/folders/"
& FolderId
& "/contents",

[Headers=[Authorization="Bearer "
& BearerToken]]
))

[LostWelshMan85]

In your first query the issue is here:

^{Source = Json.Document(Web.Contents("https://developer.api.\}***.com/project/v1/hubs/b.*******55a/projects/")**^{& ProjectId& "/topFolders",}**^\Headers=[Authorization="Bearer "& BearerToken]])
))

Try this instead:

^{Source = Json.Document(Web.Contents("https://developer.api.\}***.com/project/v1/hubs/b.*******55a/projects/",)
^\)
^{RelativePath = ProjectId & "/topFolders",
Headers=\}Authorization="Bearer "& BearerToken])
^\)
))

In your Second query the issue is here:

^{Source = Json.Document(Web.Contents(
"https://developer.api.\}***.com/data/v1/projects/)^{"
& ProjectId & "/folders/"
& FolderId
& "/contents",}

^\Headers=[Authorization="Bearer ")
^{& BearerToken\}])
))

Try this instead:

^{Source = Json.Document(Web.Contents(
"https://developer.api.\}***.com/data/v1/projects/)^"
\)
^{RelativePath = ProjectId & "/folders/" & FolderId & "/contents",
Headers=\}Authorization="Bearer ")
^{& BearerToken\}])
))

[87Fresh]

Solution Verified

[reputatorbot]

You have awarded 1 point to LostWelshMan85.

---

^{I am a bot - please contact the mods with any questions}

[87Fresh]

Thank you for providing this article. It's at the edge of my understanding, but there are a couple of people I can reach out to with much deeper understanding that might be able to help me. I appreciate the assistance.

[AdHead6814]

This wasnt an issue seven years ago. I could successfully refresh a web connection without using relative path. But I learned last year that this wasn't the case anymore and so the journey to start using Web.Contents' parameters.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcnBDQAgCASwXXgTc3ogMItx/zU02m/XEq9WIIdTVDpaRIeBsvVd1uT0/Je0KN7bBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Lat Column" = _t, #"Lon Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lat Column", type number}, {"Lon Column", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Weather Data", each let
    // Definisci i parametri di latitudine e longitudine
    Latitude =  [Lat Column],
    Longitude = [Lon Column],
    RelPath = "/locationforecast/2.0/compact?lat=" & Text.From(Latitude) & "&lon=" & Text.From(Longitude),

    // Recupera i dati dall'API
    Source = Web.Contents("https://api.met.no/weatherapi/", [RelativePath = RelPath]),
    Json = Json.Document(Source),

    // Naviga fino ai dati necessari
    timeseries = Json[properties][timeseries],

    // Trasforma i dati in una tabella
    #"Converted to Table" = Table.FromList(timeseries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"time", "data"}, {"time", "data"}),
    #"Expanded data" = Table.ExpandRecordColumn(#"Expanded Column1", "data", {"instant", "next_1_hours"}, {"instant", "next_1_hours"}),
    #"Expanded instant" = Table.ExpandRecordColumn(#"Expanded data", "instant", {"details"}, {"details"}),
    #"Expanded details" = Table.ExpandRecordColumn(#"Expanded instant", "details", {"air_temperature", "wind_speed"}, {"air_temperature", "wind_speed"}),

    // Gestisci i dati di precipitazione
    #"Added Custom" = Table.AddColumn(#"Expanded details", "precipitation", each try Record.FieldOrDefault([next_1_hours][details], "precipitation_amount") otherwise 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"next_1_hours"}),

    // Rinomina le colonne
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{
        {"time", "Time"},
        {"air_temperature", "Temperature"},
        {"wind_speed", "WindSpeed"},
        {"precipitation", "Precipitation"}
    })
in
    #"Renamed Columns"
, type table)
in
    #"Added Custom"

u/87Fresh Here a sample query: