cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AntoineCh
Helper I
Helper I

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 !

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
v-yuezhe-msft
Microsoft
Microsoft

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 ?

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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 ?

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

That works !

 

Thank you very much.

 

Antoine

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/)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.