Check if query exists

[AntoineCh]

Hi all,

I have a report with 2 elements in PowerQuery :

• A function returning a table ==> creating queries "Invoked Function", "Invoked Function (2)",  "Invoked Function (3)", etc.
• A query that appends all results of invoked functions

The query thus needs to check if every occurence of the invoked function exists, so I'm looking for a way to write :

queries = {"Invoked Function", "Invoked Function (2)", "Invoked Function (3)", ... , "Invoked Function (10)"},

if exists(#queries{i}) then A else B

Both steps can be done through List.Accumulate, but I can't find a way to test if the query exists.

I guess another similar would be : how to refer to a query from a string ? The following doesn't work :

my_string = "that_query",

source = #my_string          (#"that_query" does work)

Thanks !

[v-yuezhe-msft]

Hi @AntoineCh,

Based on your description, each of your invoke function returns a table and you want to check if the function returns any result, right? If that is the case, you can consider to use Table.IsEmpty function to check if the table contains any rows, and create another query that contains all of your functions and set a function parameter in order to check if each function returns empty table.  You can take a look at this similar thread to get more ideas.

if Table.IsEmpty(tablename)=true then A else B

Thanks,
Lydia Zhang

[AntoineCh]

Hi Lydia,

Thanks for your help !

I'm still stuck at the moment. What I'm trying to do is something like :

new_sales = List.Accumulate({2, 5}, {}, (state, current) =>
                          if Table.IsEmpty(#"Invoked Function (" & current & ")") then
                                 List.Combine({state, {#"Invoked Function"}})
                          else
                                 state),

result = Table.Combine(new_sales)

This throws an error because it's not the proper way to call a table from a string. But I don't know the function that does that, something like Table.Select( TableName as string ) .

So basically the script would create a list of all existing tables, and then append them into one result.

How can this be done ?

[ImkeF]

Hi @AntoineCh,

Have you tried the error-handler: "try...otherwise"?:

new_sales = List.Accumulate({2, 5}, {}, (state, current) =>
                          try List.Combine({state, {#"Invoked Function"}})
                          otherwise
                                 state),

result = Table.Combine(new_sales)

May I ask how these different number of queries are created? All by one function call or will the users execute the same function multiple times (with different parameters)?

[AntoineCh]

Hi @ImkeF,

Those queries are created by calling one function several times. Some are then deleted. You may therefore end up with a list of queries looking like this :

• "Invoked Function (2)"
• "Invoked Function (4)"
• "Invoked Function (7)"

I'm trying to loop through a range (say 1 to 10), identify the existing corresponding queries (2, 4, 7 in this case), and append them to a general table. "try ... otherwise" is part of the answer, but i'm missing a way to loop through all the potential query names and check if the corresponding table exist.

The general purpose of this is to append a new row to my observations table from a powerquery form (the invoked function). Maybe there's a better way to do that ?

[ImkeF]

Hi @AntoineCh,

this will return a list of all queries in your file that start with "Invoked":

List.Select(Record.FieldNames(#shared), each Text.Start(_, 7)="Invoked")

You can use this directly like this:

Table.Combine(List.Select(Record.FieldNames(#shared), each Text.Start(_, 7)="Invoked"))

Is is important that the names of your queries don't start with a text-string that is also part of the existing functions (who will be part of #shared). Using "Invoked" will be suitable.

So you're disabling "Refresh on Report Refresh" on all these queries?

Will publish a blogpost over the weekend with an alternative approach, which might suit you as well.

[AntoineCh]

Hi ImkeF,

Thank you, that #shared command is a big step forward. 

Unfortunately, * List.Select(Record.FieldNames(#shared), each Text.Start(_, 7)="Invoked") * returns a list of text values, whereas Table.Combine expects tables. I therefore get the following error : "We cannot convert the value "Invoked Function" to type Table.".

I still need a way to select a table from a table name, right ?

Great news for your post, I'll be very interested in reading it !

Antoine

[ImkeF · Accepted solution]

Hi @AntoineCh,

sorry, that was a bit quick. You're right, we need to fetch the tables. So it looks like this:

Table.Combine(List.Transform(List.Select(Record.FieldNames(#shared), each Text.Start(_, 7)="Invoked"), each Record.Field(#shared, _)))

[AntoineCh]

That works !

Thank you very much.

Antoine

[ImkeF]

Hi @AntoineCh,

I was a bit surprised to hear that this worked, as I thought you were trying to implement a sort of an imcremental load here 🙂

At least with the current PBI version, this doesn't work on my PC, as the freezing of the loaded query doesn't seem to work once they are referenced by the Table.Combine query. Pls let me know if that was your intended strategy and - if it works for you - which version you are using.

(You can find the alternative solution here: http://www.thebiccountant.com/2016/11/15/incremental-load-powerbi/)