Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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 @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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.