Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
51.6k views
in Power BI by 15 17 24
In Power Query Editor, I have one query that references a table from SQL server data source, this table holds only one row with two columns, the data in this table is changed dynamically from the data source, and I need to add get a value from this table and use it in a custom column in another table!

Is it possible to reference a column in another table in a custom column using M instead of DAX?

1 Answer

1 like 0 dislike
by 54 119 241
selected by
 
Best answer

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:

add a column from another table in Power Query

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

how to add a column from another table in Power Query

And the output should be like below

Get value from another column in another table

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:

Get value from another column in another table in a custom column in Power Query Editor

And the output should be like below

How to Reference a column in another table in a custom column in Power Query Power BI

You might also like to read Power BI: Custom Column Vs Calculated Column


See Also

by 15 17 24
0 0
Thanks Mohamed, Great answer as usual!
If you don’t ask, the answer is always NO!
...