Forum Discussion

ImaKickUrBut's avatar
ImaKickUrBut
Frequent Visitor
1 year ago
Solved

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 ...
Need Help
Tips and Tricks
  • adudani's avatar
    adudani
    1 year ago

    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.

     

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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.

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

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!