Invoke custom function > loop through a list of values that will each result in a separate query

[PaginatedDino]

Hi,

Sorry if this is maybe posted already, but I cannot seem to find it. At this point I'm thinking it's not possible by design.

Let's paint the following situation: I have a star schema model designed in Synapse or Databricks hive. I have created views as an abstraction layer that does proper renaming and so on. This means I could fetch the needed views without needing to do anything major anymore in Power Query. 

I would like to introduce parameters like server host name to be able to easily switch the connection. So I have created some parameters and have a custom function like: 

(ViewName)=>
let
    Source = Sql.Databases(SynapseServer),
    synapseProduction = Source{[Name=SynapseDB]}[Data],
    ViewImport = synapseProduction{[Schema="DWH",Item=ViewName]}[Data]
in
    ViewImport

Now I had the simple idea to invoke the function based on a list of views I would want to import, so loop through the list and output per view a separate query/table with a different data structure (columns). But it seems I cannot find away besides manually invoking the function via the GUI. 

Am I missing something? Thanks in advance for your feedback!

Is this possible?

[PaginatedDino]

Hi @BA_Pete @Anonymous ,

Thanks for your feedback. 

Yes it's in the direction you mention. I saw invoke function as a new column based on a value in the existing column like: 

The 2 views have a different column structure, so expanding it 'denormalizes/unions' it. But in fact I want a separate table/query per view name. I could start duplicating and copying manually, but that's what I want to avoid with a programmatic fix 🙂 

By the way the code that expands the above views in a denormalized/unioned way. This version is based on databricks views as opposed to the Synapse version stated initially:

let
    Query1 = Databricks.Catalogs(serverhostname, httppath, [Catalog=null, Database=null, EnableExperimentalFlagsV1_1_0=null, EnableQueryResultDownload="0"]),
    hive_metastore_Database = Query1{[Name="hive_metastore",Kind="Database"]}[Data],
    powerbi_Schema = hive_metastore_Database{[Name="dw",Kind="Schema"]}[Data],
    #"Filtered Rows" = Table.SelectRows(powerbi_Schema, each ([Name] = "viewA" or [Name] = "viewB")),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", List.Union(List.Transform(#"Filtered Rows"[Data], each Table.ColumnNames(_))))
in
    #"Expanded Data"

Thanks

[BA_Pete]

Hi  , 

As far as I'm aware, there's no M code that can spawn a new query programmatically. Power Query isn't really designed for this type of object control.

You could set up a number of dummy queries that have sources something like this:

Source = masterQuery{0}

Source = masterQuery{1}

...

However, you can see that the dummy queries would already have to be set up, and you would need to implement error handling for when there wasn't as many rows in your master query as there were dummy queries.

You *MAY* be able to do something clever with a macro of sorts (VBA/Python???), but that's beyond my skillset I'm afraid.

Pete

[PaginatedDino]

Hi Pete,

Yea I was already a bit afraid that this would be the outcome... in the meanwhile I just did the manual trick:

1) create custom function (parametrized)

= (ViewName) =>
let 
    Source = Databricks.Catalogs(serverhostname, httppath, [Catalog=null, Database=null, EnableExperimentalFlagsV1_1_0=null, EnableQueryResultDownload="0"]),
    hive_metastore_Database = Source{[Name="hive_metastore",Kind="Database"]}[Data],
    powerbi_Schema = hive_metastore_Database{[Name=databricksviewschema,Kind="Schema"]}[Data],
    Table = powerbi_Schema{[Name=ViewName,Kind="Table"]}[Data]
in
    Table

2) one by one invoke that function with a viewname (did it about 30 times 🙂 )

Thanks for the suggestions. 

[Anonymous]

just out of curiosity, what is the need (usefulness?) to have 30 distinct queries instead of a list of tables on which to act programmatically?

perhaps  here  you may find something useful

[PaginatedDino · Accepted solution]

Hi,

The need to do this programmatically and not via the GUI, is that I need to for example:

- Add parameters for those 30 queries, it might be it's not the same for all 30

- Add extended properties like: (which is related to a new cloud fetch feature and made our stuff crash by default)

EnableQueryResultDownload="0"

I don't see a way at this point to do that without programming it? Or am I missing something?

Thanks for the push in the VBA direction. I have generalized the code a bit so that the rest of the forum could try it out if need be. The example I have used is based on the following public API which returns random pictures of dogs based on a chosen breed.

So the basic steps are: 

• Start with Excel/VBA to
  1. Add in Power Query a parameter called BaseURL (in real life for example my server name or Databricks URL)

 ActiveWorkbook.Queries.Add Name:="BaseURL", Formula:= _
 """https://dog.ceo/api/breed/"" meta [IsParameterQuery=true, Type=""Text"", IsParameterQueryRequired=true]"

• Add in a worksheet the dog breeds for which I wanted an API response (in real life for example the tables I want to import)

• Add and run the code that takes into account the parameter and the variable (current breed) to add a query per breed

Sub url()
For Each breed In Sheets("Input").Range("A1:A6")
    ActiveWorkbook.Queries.Add Name:=breed, Formula:= _
    "let" & Chr(13) & Chr(10) & "    Source = Json.Document(Web.Contents(BaseURL & " & """" & breed & "/images""))," & Chr(13) & "" & Chr(10) & "message = Source[message]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " message"
       
Next
    
End Sub

Result:

Then to finally convert your Excel Model to a Power BI dataset: 

(28) Easily convert your Excel Power Pivot model to Power BI - YouTube

Cheers

[Anonymous]

I may not have understood your necessity well, but I try.
You could create a table with a column containing the url you want to access.
Then add a column where for each row you call your function on the url of that row.

[BA_Pete]

Hi @PaginatedDino ,

I may have misunderstood your requirement, but it sounds like, given a query with a column of view names (your parameter values) you could go to Add Column tab > Invoke Custom Function and choose your first column as the parameter value. This should create a nested table on each row as defined by your parameter.

Once you get to here, you can reference/duplicate/filter this query to split out different tables to different places etc.

Pete