Forum Discussion
Add column to Merged Query when sub query contains text
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!
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.
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 false4- 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.
6 Replies
= Table.AddColumn(#"Merged Queries", "Butter?", each List.Contains([QueryB][ingredientname],"butter"))
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.
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 false4- 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.
How this has been accepted as a solution is beyond me!
As Marlon Brando might have said in Paris: Où est le beurre?
you were right to call this out. Although Bibiano_Geraldo's solution was functional, after retrying adudani 's solution, it was the better one.
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!
