Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I have a report with 2 elements in PowerQuery :
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 !
Solved! Go to 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
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
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 :
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
61 | |
54 | |
27 | |
16 | |
10 |