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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.