Forum Discussion
Refer to current Query Name in formula
Hi,
I have a set of queries that get historic data from a file.
Next to that I have a set of queries that get data from the web.
The queries from historic data are called "xxx-xxx1" & "xxx-xxx2"
The queries from the web are called "web xxx-xxx1". & "web xxx-xxx2"
After doing some transformations, I open query "web xxx-xxx1" and append it with query"xxx-xxx1". Then I do the same for query 2, 3, 4, 5, etc. This is always the same for each query. Therefore, to keep the code universal, and low maintenance, my question is:
Can I use a formula in Power Query, to refer to the query name?
If so, then I would be able to extra the "xxx-xxx 1"part from "web xxx-xxx1"
Thanks in advance!
Rick
Hi,
To my knowledge, a query can't refer to its own name directly.
But I think you can achieve what you're trying to do using the Expression.Evaluate function, as long as you have a way of coming up with a list/table containing the table names as text values.
Here's an idea which could be a starting point.
If you store the names of two tables to be appended in Table1Name and Table2Name, then this expression will append those two tables:
Expression.Evaluate( Table1Name, #shared ) & Expression.Evaluate ( Table2Name, #shared )
or alternatively
Expression.Evaluate( Table1Name & " & " & Table2Name, #shared )
You could have a table with rows containing the pairs of table names, and use Expression.Evaluate within a custom column.
Anyway, that may be useful in some way.
Regards,
Owen
You can use #sections in Power Query to find the list of table names, and refer to it dynamically. Solved!
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
7 Replies
You can use #sections in Power Query to find the list of table names, and refer to it dynamically. Solved!
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
A step-by-step on how to do this: Get List of Queries in Power BI - RADACAD
I am encountering an issue with this in that #sections only exists within the power query editor. When I append the tables using expression.evaluate on the query created with #sections, it is fine within the editor, but upon closing and loading, it throws an error for trying to convert from null to table.
Have you found a way around this when using #sections?
Hi,
To my knowledge, a query can't refer to its own name directly.
But I think you can achieve what you're trying to do using the Expression.Evaluate function, as long as you have a way of coming up with a list/table containing the table names as text values.
Here's an idea which could be a starting point.
If you store the names of two tables to be appended in Table1Name and Table2Name, then this expression will append those two tables:
Expression.Evaluate( Table1Name, #shared ) & Expression.Evaluate ( Table2Name, #shared )
or alternatively
Expression.Evaluate( Table1Name & " & " & Table2Name, #shared )
You could have a table with rows containing the pairs of table names, and use Expression.Evaluate within a custom column.
Anyway, that may be useful in some way.
Regards,
Owen
Thanks for suggesting that. This might work. It's not completely what I hoped for, but I'm going to give it a try.
Appreciate the input!
Rick
> To my knowledge, a query can't refer to its own name directly.
Is this still the case?
I too wish this were possible, as it would save time and also make the code more maintainable. Here is an idea you could vote for: Microsoft Idea (powerbi.com)
