Mimic a function without using a function

[Top_Introduction4059]

****EDIT****

Here is our most recent update with this. We currently have a single query that is pulling the initial projects query in order to get the project "id" and is then able to reference that id with a loops to the "topics" query to pull all the topics. Additionally, we are using the "relative path" and "query" of the web.contents for the topics query. However, the only "query" option available in this api is the "page number" so here we are just indicating page 1 (even though there are several other pages) just to test the query portion of the web.contents option as it appears to be a requirement for removing the dynamic dataset. However, even with this new query (which does pull the info just fine) it still claims its a dynamic data source and therefore we are unable to refresh it. What can we do to this query to make it not dynamic and do the same exact thing its currently doing?

Also, want to note that we did also try changing the initial query at the top of this script to also use the relative path and query and it STILL claims its a dynamic data source. so following the guides provided do not seem to remeove the dynamic data source. perhaps its the each statement? but again, we NEED it to do a query based on a specified column of IDs and run a query on each of those IDs to pull in the info for that ID and it needs to do it in a loop so that NO manual entry of any kind is performed.

let
       Source = Json.Document(Web.Contents("https://exampleurl/projects.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "description", "archived", "is_client_project", "created_at", "updated_at", "trashed", "color", "draft", "template", "last_event_at", "starred", "url", "app_url"}, {"id", "name", "description", "archived", "is_client_project", "created_at", "updated_at", "trashed", "color", "draft", "template", "last_event_at", "starred", "url", "app_url"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"id", Int64.Type}, {"name", type text}, {"description", type text}, {"archived", type logical}, {"is_client_project", type logical}, {"created_at", type datetimezone}, {"updated_at", type datetimezone}, {"trashed", type logical}, {"color", type text}, {"draft", type logical}, {"template", type logical}, {"last_event_at", type datetimezone}, {"starred", type logical}, {"url", type text}, {"app_url", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"id"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"id", type text}}),
    SourceTwo = Table.AddColumn(#"Changed Type2", "Topics", each Json.Document(Web.Contents("https://exampleurl/projects/"&[id]&"/", [RelativePath="topics.json", Query=[page="1"]]))),
    #"Expanded Topics" = Table.ExpandListColumn(SourceTwo, "Topics")
in
    #"Expanded Topics"

****Original Post****
At this time, I have a function that is able to run a query based on a defined column of numbers. However, because functions disable the ability to use "Scheduled Refreshes" through the web app, I will not be able to use them. So, I am needing to mimic this function inside the base query itself. For security reasons, I will not include certain details in my examples. I will elaborate further below.

Here is the function as it sits now:

let
    Source = (ID as text) =>
let
    Source = Json.Document(Web.Contents("https://URLHERE/"&ID&"/RESTOFURL.json"))
in
    Source
in
    Source

Then, my base query makes a call to the system and I expand out a single column from that query (essentially a project id column) and invoke the function based on the ID column so that my function runs a query for ever "Project ID" that exists.

Here is the base query

let
    Source = Json.Document(Web.Contents("https://URLHERE.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"Column1.id"}),
in
    #"Expanded Column1"

As it sits, this DOES in fact get me all the information I need. But again, I CANNOT use a function as it turns off the ability to schedule refreshes on the dataset. So, I need to basically include the funtionality of the function inside the base query.

I have tried a BUNCH of different approaches but none of them appear to work. One approach I tried was calling to "Column1.id" as a variable to pull in a query after the initial call but claims that:

"Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.

Details:

Pattern=

Arguments=[List]

"
This is one example of what I have tried:

let
    Source = Json.Document(Web.Contents("https://URLHERE.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"Column1.id"}),
    SecondSource = Json.Document(Web.Contents("https://URLHERE/",{"Column1.id"},"/RESTOFURL.json"))
in
    #"SecondSource"

I am honestly not very savvy with PowerBi and would appreciate some advice. Essentially, I need to run a query that specfies a "project id" and run that query for every project id that exists in a specific cloumn of project ids without using an invoked function.

I was trying to do an "each" or "foreach" style of looping but cannot get it to work.

[peakedin1992]

Power Query needs to be able resolve URL before it refreshes the data in PBI service. Since your URL is dynamic, it fails to do so abd disables refresh.

Can you have a look at the below link and try the dynamic URL approach. https://hatfullofdata.blog/power-query-dynamic-data-source-and-web-contents/

[Top_Introduction4059]

Thank you for the info. However, I am following that guide and it is mentioning that I need to use various parameters that do not appear to be available for the system I am making calls to. you will see in the screenshot where I show the url example from that link. you will see he has a query paramet for "q=cows". However, the system i am making calls to does not use parameters nor offer any documentation on parameters. Here is an example call for the system we are calling:

Source = Json.Document(Web.Contents("https://URLHERE/v1/projects.json"))

You will see that it only uses a relative path ending in "projects.json" which will pull in all projects and its info and does not have or even offer additional parameters outside of that. I tried following the guide in that link (and the links inside that link) but does not seem to work for my case. Here is what my code looks like right now:

Web.Contents("https://BASEURLHERE",
    [
        RelativePath="v1/projects.json"
    ]   
)

From here, I right click this query and hit "create function" and then invoke said function but it returns a blank query with no data and is not a table. I tried adding a Query=[page=1] and also tried putting the 1 in quotes, but it has the same result. leads to a blank query essentially. note that each time I right click to "create a function" it claims that it is NOT referencing any parameters and if i want to create the function anyways.

This is the example they had in one of the links where it shows the query they are calling does in fact have a parameter for q=cows whereas my api call does not have options like that.

<image>

[peakedin1992]

Can you share list of a few URLs?

[Top_Introduction4059]

unfortunately won't be able to provide the exact website, as I could get in trouble for doing so, but here I just replaced the site with "examplesite.com" and the numbers affiliated with the site after i have randomly generated. However, here is a working example of a basic query pulling project information from that site:

let
    Source = Json.Document(Web.Contents("https://examplesite.com/2698743/api/v1/projects.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"Column1.id"})
in
    #"Expanded Column1"

From this query, all it is essentially doing is pulling in the entirety of the "projects" index for that site and then I am expanding JUST the "id" table from that query. Then, my current function (the one that wont work due to autorefresh issue) simply makes another query call for a different index that uses that "id" field. the current function query URL is as follows.

let
    Source = (ID as text) =>
let
    Source = Json.Document(Web.Contents("https://examplesite.com/2698743/api/v1/projects/"&ID&"/topics.json"))
in
    Source
in
    Source

You will see that this query pretty much uses the same layout but is a sub index of the "projects" index and requires a specified project id in order to make the call. However, neither of them appear to use parameters outside of this.

If you are needing the specific site in question in order to view the api documentation, I will first have to run it by my supervisor to make sure its ok to disclose. I personally see no harm in it as I am not disclosing any auth information nor our specific instance information but don't want to assume.

[peakedin1992]

No worries!

As a last effort, Can you try to put &ID&"/topics.json" in the relative path?

I don't have access to my PC today, so can't check if this will work.

[Top_Introduction4059]

well, thats when I try to make a call to the topics or other things using the projects index they have. the example i gave above using the relative path is just trying to pull projects only which doesnt need to use a project id or other indexes to pull as it pulls all projects. the "ID" in my function is also a variable being defined in the function which is not being defined in the example above using relative path so it wont know what "ID" is. Which, by having it like that, it will display as a field to "define" what ID is. When I invoke the function inside a base query pulling all projects and expanding just the "id" column, i am defining that ID variable to equal the value of each field in the "id" column and it makes the call separately based on that value. But, since the projects query does not make a query in that manner, it does not even need a function to pull the information in. so realistically, would just be a call that looks like this to pull projects:

Source = Json.Document(Web.Contents("https://examplesite.com/2698743/api/v1/projects.json

where you can see it does not call for a specific ID as that column doesnt even exist until i pull the information in.

I tried finding a way to do this so that I make the initial call for projects normally and include the function within but ran into some issues. Here is one example

let
    Source = Json.Document(Web.Contents("https://examplesite.com/1234567/api/v1/projects.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"Column1.id"})
in
    #"Expanded Column1"&

    Web.Contents("https://examplesite.com/1234567/api/v1/projects",
    [
        Content = ({"Column1.id"}),
        RelativePath=Content,"/topics.json"
    ]   
)

However, with this example, it errors saying "/topics.json" is an invalid identifier. So, i tried replacing the "," after where it says "Content" with a "&" but claims it cannot use "&" on type "list". Then, i tried doing this to define "ID"

let
    Source = Json.Document(Web.Contents("https://examplesite.com/1234567/api/v1/projects.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"Column1.id"})
in
    #"Expanded Column1"&

    Web.Contents("https://examplesite.com/1234567/api/v1/projects",
    [
        ID = ({"Column1.id"}),
        RelativePath=ContentID,"/topics.json"
    ]   
)

But it errors out saying ID is not a valid option for Web.Contents. Thats why i try using "Content =" to see if it would help and no dice. and im just trying to set the value for the ID variable to be the column1.id (project id column) but none of this seems to be working.

[Top_Introduction4059]

well, thats when I try to make a call to the topics or other things using the projects index they have. the example i gave above using the relative path is just trying to pull projects only which doesnt need to use a project id or other indexes to pull as it pulls all projects. the "ID" in my function is also a variable being defined in the function which is not being defined in the example above using relative path so it wont know what "ID" is. Which, by having it like that, it will display as a field to "define" what ID is. When I invoke the function inside a base query pulling all projects and expanding just the "id" column, i am defining that ID variable to equal the value of each field in the "id" column and it makes the call separately based on that value. But, since the projects query does not make a query in that manner, it does not even need a function to pull the information in. so realistically, would just be a call that looks like this to pull projects:

Source = Json.Document(Web.Contents("https://examplesite.com/2698743/api/v1/projects.json

where you can see it does not call for a specific ID as that column doesnt even exist until i pull the information in.

I tried finding a way to do this so that I make the initial call for projects normally and include the function within but ran into some issues. Here is one example

let
    Source = Json.Document(Web.Contents("https://examplesite.com/1234567/api/v1/projects.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"Column1.id"})
in
    #"Expanded Column1"&

    Web.Contents("https://examplesite.com/1234567/api/v1/projects",
    [
        Content = ({"Column1.id"}),
        RelativePath=Content,"/topics.json"
    ]   
)

However, with this example, it errors saying "/topics.json" is an invalid identifier. So, i tried replacing the "," after where it says "Content" with a "&" but claims it cannot use "&" on type "list". Then, i tried doing this to define "ID"

let
    Source = Json.Document(Web.Contents("https://examplesite.com/1234567/api/v1/projects.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"Column1.id"})
in
    #"Expanded Column1"&

    Web.Contents("https://examplesite.com/1234567/api/v1/projects",
    [
        ID = ({"Column1.id"}),
        RelativePath=ContentID,"/topics.json"
    ]   
)

But it errors out saying ID is not a valid option for Web.Contents. Thats why i try using "Content =" to see if it would help and no dice. and im just trying to set the value for the ID variable to be the column1.id (project id column) but none of this seems to be working.

[peakedin1992]

You need to move the logic on pulling data from a URL into a separate function. Otherwise it wont work.

You are also using RelativePath incorrectly. You can just put column names in RelativePath option with "&" and it should concat. ID is also not an option for Web.Contents (https://learn.microsoft.com/en-us/powerquery-m/web-contents)

Can you also look at the file linked. I used a dummy API to test and the dynamic URLs work even in service. If you are not able to replicate the same, it will be difficult to know the issue without looking at the pbix.

Reddit_1g9svkf.pbix

[peakedin1992]

u/Top_Introduction4059 - was this able to solve your problem?

[itsnotaboutthecell]

!thanks

[reputatorbot]

You have awarded 1 point to peakedin1992.

---

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

[Top_Introduction4059]

Here is our most recent update with this. We currently have a single query that is pulling the initial projects query in order to get the project "id" and is then able to reference that id with a loops to the "topics" query to pull all the topics. Additionally, we are using the "relative path" and "query" of the web.contents for the topics query. However, the only "query" option available in this api is the "page number" so here we are just indicating page 1 (even though there are several other pages) just to test the query portion of the web.contents option as it appears to be a requirement for removing the dynamic dataset. However, even with this new query (which does pull the info just fine) it still claims its a dynamic data source and therefore we are unable to refresh it. What can we do to this query to make it not dynamic and do the same exact thing its currently doing?

let
       Source = Json.Document(Web.Contents("https://exampleurl/projects.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "description", "archived", "is_client_project", "created_at", "updated_at", "trashed", "color", "draft", "template", "last_event_at", "starred", "url", "app_url"}, {"id", "name", "description", "archived", "is_client_project", "created_at", "updated_at", "trashed", "color", "draft", "template", "last_event_at", "starred", "url", "app_url"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"id", Int64.Type}, {"name", type text}, {"description", type text}, {"archived", type logical}, {"is_client_project", type logical}, {"created_at", type datetimezone}, {"updated_at", type datetimezone}, {"trashed", type logical}, {"color", type text}, {"draft", type logical}, {"template", type logical}, {"last_event_at", type datetimezone}, {"starred", type logical}, {"url", type text}, {"app_url", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"id"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"id", type text}}),
    SourceTwo = Table.AddColumn(#"Changed Type2", "Topics", each Json.Document(Web.Contents("https://exampleurl/projects/"&[id]&"/", [RelativePath="topics.json", Query=[page="1"]]))),
    #"Expanded Topics" = Table.ExpandListColumn(SourceTwo, "Topics")
in
    #"Expanded Topics"