Connection in power query

[ngocnguyen]

Hi. 

I have 2 tables: 1st table (name: Page):  loading from Power query into Power Pivot

                         2nd table (name: Raw_LT):  loading from excel file into Power Pivot

Both table have 1 column (name: Index) that have the same information

I already make connection between 2 table in power Pivot

However, when I use  Dax formulas  in 1st table  : =Related(Raw_LT[Region]), it was advised that the Raw_LT doesn't exit or there is no connection between 2 tables?

Is there any mistake that I made here ? and how to fix it.

Thanks in advance for your support

[v-diye-msft · Accepted solution]

Hi @ngocnguyen 

I can't access the file due to the limited permission.

The relationship between 2 tables is one -> many. 

Which means that for each row in index you can have more than one row in another index.

Therefore, you must use RELATEDTABLE instead of related, And since this is going to return more than one row , You should wrap it in an aggregation function.

You can also refer to below similar threads for references:

https://community.powerbi.com/t5/Desktop/Table-Relationship-Doesn-t-Work/td-p/593189 

https://stackoverflow.com/questions/52119240/power-bi-query-error-the-column-either-doesnt-exist-or-... 

[amitchandak]

@ngocnguyen , related work from 1-M direction. I do not think it works form M to 1

in such If I have move data from one table to another in new column we use

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

[ngocnguyen]

@amitchandakAs I checked again, it is actual M to 1 direction. However, I wonder why it is M to 1 direction, not 1 to M or 1 to 1 direction because I create 1 colume" Index" in both file that contain the unique value only.

So, is there any way to change it into 1 to M or 1 to 1 direction in Power Pivot?

P/s: If I load 2 tables from excel, there is no problem when using Dax Related. However, If 1st table load from Power query, 2nd table load from Excel, the above issue happens

[Pragati11]

Hi @ngocnguyen ,

Can you share your dax expression please, if you are using any?

Did you try making the relationship bi-dirrectional.?

Thanks,

Pragati

[ngocnguyen]

https://jppanasonic-my.sharepoint.com/:x:/r/personal/thanhngoc_nguyen_vn_panasonic_com/_layouts/15/g...

@Pragati11, About is my file. I try to make the relationship between 2 tables, but it can not work

Pls help