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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
josetabuenca
Frequent Visitor

add a column in table stored in direct query with data from another table stored in import mode

Hi

 

Stiill a newbie in BI.

 

Table 1 is a table stored in direct query. 

ID
1

2

3
4
5
6
7
8
9
10

 

Table 2 is stored in import mode

IDCATEGORY
4ES
7PR
9TR
10SD

 

I would like to add a column in table 1 obtaining the following result

IDCATEGORY
1N/A
2N/A
3N/A
4ES
5N/A
6N/A
7PR
8N/A
9TR
10SD

 

I have been trying to create columns into table 1 with conditions towards table 2 without success.

 

Any idea on how to do it?

 

Cheers.

9 REPLIES 9
josetabuenca
Frequent Visitor

Can someone help here?. Solutions provided so far didn't work

v-yanjiang-msft
Community Support
Community Support

Hi @josetabuenca ,

Please try this one:

CATEGORY =
COALESCE (
    MAXX (
        FILTER ( 'Table2', 'Table2'[ID] = EARLIER ( Table1[ID] ) ),
        'Table2'[CATEGORY]
    ),
    "N/A"
)

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yanjiang-msft 

 

Thanks a lot for your support.

 

I am obtainining same error message than before

 

The column 'Table1'[CATEGORY] cannot be pushed to the remote data source and cannot be used in this scenario.

 

Guessing here that Table1 stored in Direct Query is having an effect here.

 

 

v-yanjiang-msft
Community Support
Community Support

Hi @josetabuenca ,

According to your description, here's my solution.

Add a calculated column in Table1:

CATEGORY =
COALESCE (
    LOOKUPVALUE ( 'Table2'[CATEGORY], 'Table2'[ID], 'Table1'[ID] ),
    "N/A"
)

It has no matter with the relationship, get the correct result:

vkalyjmsft_0-1670566403878.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi

 

Solution does not work. I receive following message 

The column 'Table1'[CATEGORY] cannot be pushed to the remote data source and cannot be used in this scenario.

amitchandak
Super User
Super User

@josetabuenca , if relation between table 2 and table 1 is 1 to Many

 

then yoy can try

 

related(Table2[CATEGORY])

 

or

 

coalesce(related(Table2[CATEGORY]) , "N/A")

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi

 

Thanks for your swift reply.

 

The relationship between Table 2 and Table 1 is Many to 1.

I cannot change the relation to 1 to Many. I got following error message: The cardinality selected isn't valid for this relationship.

 

Some another idea here?

 

Regards

@amitchandak 

I have been able to change the relation to 1 to Many by data cleaning ID column in Table 2 (remove duplicates and remove nulls),

However solution is still not working, I am obtaining following error message

 

The column 'Table1[CATEGORY]' either doesn't exist or doesn't have a relationship to any table available in the current context.

 

There us a relationship 1 to many between Table 2 and Table 1.

 

Some idea here?

correction

 

error message is The column 'Table2[CATEGORY]' either doesn't exist or doesn't have a relationship to any table available in the current context.

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors