Create a calculated column based on the values matching another table

[Anonymous]

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.

[Smauro]

Hi @Anonymous 

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 @Anonymous'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

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

Hi @Anonymous  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.

[Anonymous]

Hi @Anonymous ,

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.

[Anonymous · Accepted solution]

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.