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, _)))
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

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

    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'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.