Advanced Editor - Add Column from Related Table

[Anonymous]

Added a column from a related table but found out it is not available in Merge tables dialog (Ex: Column = RELATED('Account'[SubRegion]) ).  Found a suggestion to add the column using the Advanced Editor, but editor flags the single quote in the table name.  Anyone have an example of using RELATED in the advanced editor to add a column?  The needed column is two joins over in the relationship diagram.  Data Source is OpportunityLineItem in SalesForce; so SQL views are less than convenient.

Advanced editor failed attempt:

,SubRegion = Table.AddColumn(RELATED('Account'[SubRegion]))
in

Thanks,

Smitty

[v-ljerr-msft]

Hi @Anonymous,

As @MarcelBeug has mentioned above, you may mix up DAX (RELATED) and Power Query (Advancd Editor). If you want to use RELATED(DAX) function, you can just use the formula below to add a new calculate column under Modeling tab. 

SubRegion = RELATED ( 'Account'[SubRegion] )

Regards

[Anonymous · Accepted solution]

Yes, I was mixing DAX with M.  The end objective is to merge two tables, but Custom columns are not available in a merge.  The big picture is to merge Actuals table with Targets table to visualize the % diffs.  The Actuals table needs to join data between three tables and the required column is two joins over from the first table. 

Enter the Advanced Editor of the middle table and add a table.join before the final "in" clause to get two columns (Account Key, SubRegion) from the Account table:

#"GetSubRegion" = Table.Join(#"Replaced Value2","Account Record Key",Table.SelectColumns(#"Account", {"Account Key","SubRegion"} ), "Account Key",JoinKind.Inner)
in
    #"GetSubRegion"

Enter the Advanced Editor of the first table and add a table.join before the final "in" clause to get two columns (Opportunity Key, SubRegion) from the above table:

 #"GetSubRegion2" = Table.Join(#"GetFiscalQuarter","Opportunity LI Key",Table.SelectColumns(#"Opportunity", {"Opportunity Key","SubRegion"} ), "Opportunity Key",JoinKind.Inner)
in
    #"GetSubRegion2"

Now that SubRegion is in the first table the rest of the data can be grouped then merged with the Target table.  Any shortcuts to this effort?

HTH,

Smitty

[Diego-mx]

I was looking for this feature as well.  
While noticed that DAX and M have their contexts in which each excels, I got used to RELATED functionality and wanted to use it in M's GetData context. 

Good luck. 

[MarcelBeug]

You seem to mix up DAX (RELATED) and Power Query (Advancd Editor).

In order to add a column from another table in Power Query, use "Merge Queries" from the menu-options.

No need to do this via the Advanced Editor.