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
josetabuenca
Regular 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
Regular 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")

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