I have 2 tables that share a Foreign Key. Power BI sees them as a Many (Table A) to One (Table B) relationship. All I'm trying to do is to get a value from Table B to show up as a column for Table A.
When I look at the table via PowerQuery (using "Edit Query" in Power Bi Desktop) I can see Table B but every row just shows "Value" as it's value. If I click "Value" I get the details of the related object below the table so I know the relationship works.
My struggle is that none of the methods I've seen via google results to get that value work for me.
I've tried using LOOKUPVALUE and RELATED.
RELATED(TableB[ColumnNameImTryingToRetrieve])
RELATED(TableB[IdColumn]
For the RELATED function, every variation I try for the ColumnName parameter either results in the error message
"The column 'TableB[NameIveGiven]' either doesn't exist or doesn't have a relationship to any table available in the current context."
or the error message
"Parameter is not the correct type".
LOOKUPVALUE isn't even available as an option in the Intellisense options that come up so i can't try it.
I've seen a lot of references about LOOKUPVALUE not being available in DirectQuery mode and that there used to be an option in DirectQuery options called "Allow unrestricted measures in DirectQuery mode" but that is no longer available. This supposedly would have allowed LOOKUPVALUE to work.
Also, when I make most changes in PowerQuery when trying to add the new column I get the error message "This step results in a query that is not supported in DirectQuery mode".
Is there any simple way to get the value I'm after in DirectQuery mode or should I switch to Import Mode?
