Reference a column in another table in a custom column in Power Query Editor
In such a scenario, it would be easy to use DAX Calculated Column to reference a column from another table rather than using M in a Custom Column in Power Query.
- In DAX, you can use the
RELATED() function if the current two tables have already a relationship.
- Also, you can use the DAX
LOOKUPVALUE() function if you can’t create a relationship between the two tables.
Read more at RELATED Vs LOOKUPVALUE DAX in Power BI
However, if your requirements need to use Custom Column in Power Query Editor, so you can reference a column from another table in a Custom Column using the below formula:
#"Table Name"[Field Name]{Row Index}
If the table name is just one word, you can use the below formula
TableName[Field Name]{Row Index}
Example:
Consider you have a table called "Another Table" that has two rows and two columns as below:
So if you need to get the value of the second column and first row (In this case, the value is "Mohamed El-Qassas") and use it in a custom column in another table, so your formula should be as below
And the output should be like below
Now, consider you need to get the value of the second column and the second row (In this case, the value is "Hamza El-Qassas"), so you have to change the index of your row as below:
And the output should be like below
You might also like to read Power BI: Custom Column Vs Calculated Column
See Also