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.
I have a table A with duplicate values in one column. I want to run a vlookup in another table B, and want to return 'TRUE' if it exists in table A. eg below-
TABLE A ::
ID amount
1 10
2 15
1 25
3 38
TABLE B ::
1 TRUE
4 FALSE
Someone please help ASAP. Thanks a ton!
Abhishek
HI @abhitiwa,
You can add a calculated column with below formula to check exist item based on other table:
Check = [ID] in VALUES(Records[ID])
Regards,
Xiaoxin Sheng
That's great. Another way is to wrap VALUES() into a CALCULATE() for context transition.
*** works with relationship only, though
If @abhitiwa wants to just check if ID exists then it should be >0
Else if he is checking for duplicates then it should be>1
thanks guys for such a quick response. @Zubair_Muhammad your solution doesn't give any error, but it only returns FALSE as the value. However, most of the values are present in Table A. Can you please look?
is_sell = CALCULATE(COUNTROWS(prop),FILTER(prop,prop[user_id]=VALUE(c_users[id]))) > 0
Hi
yes, its a calculated column. And, I have used VALUE (not VALUES) to convert Text (c_users) to number. Not sure what am I missing here. I then tried to change the data type of this field to Number from Text, and then I receive a diff error as below -
this expression refers to 'sddsaggv-fgjkg-gfgmk' which has an error
What happens when you wrap both IDs in VALUE() ?
is_sell = CALCULATE ( COUNTROWS ( prop ), FILTER ( prop, VALUE (prop[user_id]) = VALUE ( c_users[id] ) ) ) > 0
One way to do this would be:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"ID"},#"TABLE A",{"ID"},"TABLE A",JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(#"Merged Queries", "Match", each if Table.First([TABLE A]) = null then "FALSE" else "TRUE"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"TABLE A"})
in
#"Removed Columns"
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.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |