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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors