Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ngocnguyen
Helper IV
Helper IV

Connection in power query

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

 

 

Capture.PNGCapture2.PNG

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

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-... 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

5 REPLIES 5
v-diye-msft
Community Support
Community Support

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-... 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
amitchandak
Super User
Super User

@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])

@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
Super User
Super User

Hi @ngocnguyen ,

 

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

Did you try making the relationship bi-dirrectional.?

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.