Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
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?
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)
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
> To my knowledge, a query can't refer to its own name directly.
Is this still the case?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |