cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

How to reference another table in Query Editor (M)

Hello,

 

I am attempting to reference a table for a custom column in the Query Editor mode of Power BI desktop.

 

The formula will be similar to the solution found in the following link

 

"= List.Average(Table.Column(Table2,[EggName]&"NO2"))"

 

What is the syntax for Table2?

 

For example, columns are referenced as [Column1] in M but what is the reference for other tables?

 

Looking forward to your responses.

 

Have a great day!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Champion
Community Champion

Just the name of the table, which is equal to the name of the query from which the table is the result.

 

If the name of the query (table) contains spaces or other special characters, the name must be included in #"....",
e.g. #"Table name".

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
Community Champion
Community Champion

Just the name of the table, which is equal to the name of the query from which the table is the result.

 

If the name of the query (table) contains spaces or other special characters, the name must be included in #"....",
e.g. #"Table name".

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

Hello,

 

It looks like that works! 

 

The problem now is I am getting an error: 

 

"Expression.Error: A cyclic reference was encountered during evaluation"

 

Here is my formula: = List.Count(List.FindText([Initiative],Table.Column(#"Master - Milestones",[IdeaID])))

 

For context, I am trying to count the number of milestones for each initiative. The initiative is the key for my current table and the foriegn key in the "Master - Milestones" table.

 

I would be grateful if you could help me resolve this error and achieve my desired output. 

 

Best,

 

Justyce

It's hard to say. Referencing a column in another table is not a problem as such.

The issue must be related to your entire query structure, e.g. 2 tables referencing each other.

Specializing in Power Query Formula Language (M)

Thank you for your replies:

 

Based on your experience, do you think this error is caused by a join between the Initiative and Milestone tables or because they are linked like a database?

 

Best,

 

Justyce

You're welcome.

 

I have no answer to your last question: I've studied Power Query Formula Language quite thoroughly, but my experience with Power Query and databases is close to zero.

 

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors