Conditional column selecting column from other table

[Anonymous]

Hello all.

I need a help.

I have two tables:

Table A with customer by entity name

Table B with customer by entity name and short name

How can I add a conditional column in Table A selecting a column from Table B (entity name) with output from Table B (short name)? 

I don't have all customer in Table B, so I need also a condition that null returns the customer (entity name) from Table A.

Thanks!

[Anonymous]

@Anonymous 

You're going to want to have a column with a unique ID that you can use to join Table A and Table B. Then you can select "Merge Queries" in the query editor, match on the unique ID, and make sure that it is the Join Kind "Left Outer (all from first, matching from second)." That way, since Table B doesn't have all of the entries that Table A has, the short name column entries that don't have a match will be replaced with a null in your new, merged table.

Is that what you want to do?

[Anonymous]

Hi @Anonymous 

I also want to change the null that I will have in the merged table by the Customer name (entity name) from Table A.

[Anonymous]

@Anonymous 

So do you have some customers in Table B that are not in Table A? If so, since you are using a left join, those customers would not appear in your merged table.

[Anonymous]

@Anonymous 

But I need to have a complete table by short name or entity name.

[Anonymous]

@Anonymous 

I think I am confused as to what your two tables contain. Does Table A overlap with Table B? I know that there are entries in Table A that are not in Table B but are there also entries in Table B that are not in Table A?

[Anonymous]

Hi @Anonymous 

I think now will be easier to understand. 

Table A		Table B			Output
Customer		Customer	Short Name		Short Name
Power BI AS		Power BI AS	Power BI		Power BI
Power BI Ltd		Power BI Ltd	Power BI		Power BI
Power BI Desktop AS		Power BI Desktop AS	Power BI		Power BI
Power BI Service AS		Power BI Service AS	Power BI		Power BI
Micro AS		Micro AS	Micro		Micro
Micro Soft Ltd		Micro Soft Ltd	Micro		Micro
Solutions AS					Solutions AS
Mobile Ltd					Mobile Ltd
Consulting Ltd					Consulting Ltd

[Anonymous · Accepted solution]

@Anonymous 

Got it! So you can merge the tables with a LEFT JOIN. Then you can create a new conditional column with the format:

IF [new column] = null THEN output=Column1, ELSE [new column]

]

[Anonymous]

Thanks, @Anonymous 

Last doubt: is it possible to have the merged column (Table2.Column2) in Table A? I am wondering if I can avoid one more table. 

[Anonymous]

@Anonymous 

I think the only way to do that without merging queries would be to add a conditional column straightaway from Table A. This would automatically give customers by entity name a short name if it included something like "power bi" or "microsoft" and return the regular entity name if it has no short name.