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 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 !

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, _)))

9 Replies

  • 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)?