Forum Discussion
Check if query exists
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)?
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.
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