Refer to current Query Name in formula

[Rickmaurinus]

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

[OwenAuger · Accepted 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

[Rickmaurinus · Accepted solution]

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.

[Anonymous]

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?

[ManoukUBN]

A step-by-step on how to do this: Get List of Queries in Power BI - RADACAD

[rockymtnrajah]

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)

[OwenAuger · Accepted 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

[andreas1]

> To my knowledge, a query can't refer to its own name directly.

Is this still the case?

[Rickmaurinus]

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