Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

  • Smauro's avatar
    Smauro
    Solution Sage
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.