Forum Discussion

AntoineCh's avatar
AntoineCh
Helper I
9 years ago
Solved

Check if query exists

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...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    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's avatar
AntoineCh
Helper I
9 years ago

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's avatar
ImkeF
Community Champion
9 years ago

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.