cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
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

NameID
AA49358
BB49372
CC49347
DD49401

 

Table B has -  Description and ID

DescriptionIDCompany
Accepted72575Other
Accepted61963Other
Later49347A
Accepted75848Other
Never49358A

 

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

I first load table A in a query:

image.png

 

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

 

image.png

 

 

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

image.png

 

This is the result:

 

 

image.png

 

 

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

View solution in original post

6 REPLIES 6
Super User I
Super User I

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:

image.png

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

 

Cheers,

smauro




Feel free to connect with me:
LinkedIn

Super User II
Super User II

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 

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

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.

 

 

Hi @Rocco_sprmnt21 ,

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.

I first load table A in a query:

image.png

 

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

 

image.png

 

 

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

image.png

 

This is the result:

 

 

image.png

 

 

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

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors