Add column to Merged Query when sub query contains text

[ImaKickUrBut]

Hello! I'm very new to PBI & need some guidance please. 

I have 2 queries, queryA & queryB - these queries are joined on recipeid. example:

queryA

recipeid	recipename	recipedate	queryb
12345	apple pie	1/1/2024	Table
99999	banana pudding	2/1/2024	Table
654654	chocolate cake	3/1/2024	Table

queryB

recipeid	ingredientname
12345	apples
12345	butter
12345	sugar
99999	bananas
99999	milk
99999	nilla wafers
654654	chocolate
654654	butter
654654	even more chololate

*I want to keep query a to one row for recipe id, with adding a new column that will return true or false based on the presence of "butter" in the queryb.ingredientname field where the recipeid matches. example output:

queryA

recipeid	recipename	recipedate	hasbutter
12345	apple pie	1/1/2024	true
99999	banana pudding	2/1/2024	false
654654	chocolate cake	3/1/2024	true

what's the best way to accomplish this please? thanks in advance!

[adudani · Accepted solution]

hi @ImaKickUrBut ,

output :

solution is attached. This solution ignores any case sensitivity. Moreover, adding more items in the list where "butter" is hardcoded in the "HasButter" step, would check for any another item added and would mark true.

I believe @p45cal , solution will also work. This is an extension with a bit more functinality, in my opinion.

If this doesn't resolve the issue, kindly expand on the required output.

[Omid_Motamedise]

To solve this problem in an easiest way, just copy and past the next code into the advance editor ()

let
    QueryA= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lFKLCjISVUoyEwFsg31DfWNDIxMgMyQxKScVKVYnWglSxAAiiQl5gGhQkFpSkpmXjpQwAibajNTEyACCiVn5Cfn5ySWpCokJ2aDDDfGUB4LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [recipeid = _t, recipename = _t, recipedate = _t, queryb = _t]),
    QueryB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc1JDoAgDAXQqxDWbBwTz2JcVFOHWIEA6vUt0ai0Xb32p20rs7woK6kkWEvoZac+6vcQ0CXk9wluaWLFI9DcPrFtoTUBvRCBOGFEd1/WVcnDm2E2gyEImPLv9Wt4oBabcSg4RE+ouwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [recipeid = _t, ingredientname = _t]),
    #"Merged Queries" = Table.NestedJoin(QueryA, {"recipeid"}, QueryB, {"recipeid"}, "Table1", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.RowCount(Table.SelectRows([Table1],(x)=>x[ingredientname]="butter"))>0)
in
    #"Added Custom"

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!

[Bibiano_Geraldo · Accepted solution]

Hi, 

Try to follow these steps:

1 - After you merge the tables, instead of expand the merge, choose agregate as shown bellow, and then select the field you want to count, in your case the [recipe id] column:

2. Your result now should look like this:

3. Now add a custom column with this code and click ok: 

if [#"Count of Item No."] > 1 then true else false

 4- Now your table should look like this:

5. now you can remove the count column:

6. Your final result, should look like this:

NOTE: Dont forget to double check the columns names and data type of the columns.

[p45cal]

How this has been accepted as a solution is beyond me!

As Marlon Brando might have said in Paris: Où est le beurre?

[ImaKickUrBut]

you were right to call this out. Although @Bibiano_Geraldo's solution was functional, after retrying @adudani 's solution, it was the better one. 

[adudani · Accepted solution]

hi @ImaKickUrBut ,

output :

solution is attached. This solution ignores any case sensitivity. Moreover, adding more items in the list where "butter" is hardcoded in the "HasButter" step, would check for any another item added and would mark true.

I believe @p45cal , solution will also work. This is an extension with a bit more functinality, in my opinion.

If this doesn't resolve the issue, kindly expand on the required output.

[p45cal]

= Table.AddColumn(#"Merged Queries", "Butter?", each List.Contains([QueryB][ingredientname],"butter"))