cancel
Showing results for
Did you mean:
Resolver I

## Create a calculated column based on the values matching another table

Hi all,

I am kind of stuck at this problem. I want to create a calculated column-"Company" if ID matches another table . For example-

Table A has - Name, ID

 Name ID AA 49358 BB 49372 CC 49347 DD 49401

Table B has -  Description and ID

 Description ID Company Accepted 72575 Other Accepted 61963 Other Later 49347 A Accepted 75848 Other Never 49358 A

I want to create a Company column if ID  in Table A is found in ID of Table B then company column should be populated as A or else it should populate as Other.

I tried using LOOKUPVALUE and VLOOKUP but wasn't able to get the desired result.

Any idea how to do this?

Thank you.

1 ACCEPTED SOLUTION
Resident Rockstar

I first load table A in a query:

and table B in another query (there is the column Company just to verify the expected result.

Then having tabB selected I add a new column via UI. The code is that you already have seen:

This is the result:

if you still have problems, in order to help you, you should post exactly all the steps you follow.

6 REPLIES 6
Solution Specialist

Hi @spsingh

It seems that you'd like a DAX Calculated column, if so, you're in the wrong place.

A Custom Column could be:

``````DAX Company =
VAR i = [ID]
VAR c =
COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table A'[ID] ), 'Table A'[ID] = i ) ) + 0
RETURN
IF ( c > 0, "A", "Other" )``````

Or, if you indeed require a pq solution, then @Rocco_sprmnt21's solution should work.

For a quicker way, since there is a function to check another table a lot quicker than refering it on every row, you should merge table A into table B by [ID] and then transform the merged column to "A" if found and "Other" if not:

``````    #"Merged Queries" = Table.NestedJoin(PreviousStep, {"ID"}, #"Table A", {"ID"}, "pq Company", JoinKind.LeftOuter),
#"Extract Company" = Table.TransformColumns(#"Merged Queries", {{"pq Company", each if Table.IsEmpty(_) then "Other" else "A", type text}})``````

Where PreviousStep is your previous powerquery step.

result:

Note that all PowerQuery ways will be a lot quicker than DAX Calculated Columns.

Cheers,

smauro

Feel free to connect with me:

Resident Rockstar

try to add a column in this way:

``````    anotherCol = Table.AddColumn(tabB, "compagnia", each if List.Contains(tabA[ID],[ID]) then "A" else "Altro")
in
anotherCol ``````
Resolver I

Hi,

Thank you for your response. I tried using it but I am getting an error. There is no relationship between the tables. I want to add a custom column in Table B. If ID of Table A matches id in Table B then it should populate as "A" or else populate as "Other".

Resident Rockstar

Hi @spsingh  you should put:

if List.Contains(tabA[ID],[ID]) then "A" else "Other"

in the UI add custom column to when table B is select.

Resolver I

I tried changing but it's still not working for me. In case you have a pbix file, could you please share it with me. May be I am still doing something wrong. I am trying to add this column in power query editor.

Resident Rockstar

I first load table A in a query:

and table B in another query (there is the column Company just to verify the expected result.

Then having tabB selected I add a new column via UI. The code is that you already have seen:

This is the result:

if you still have problems, in order to help you, you should post exactly all the steps you follow.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!