Forum Discussion

Mechi's avatar
Mechi
Icon for Helper I rankHelper I
6 years ago
Solved

Find Value in one table with Text field in another table with Filters

Hello All,

 

 I need your help to find a solution for the  below query in M code 

 

Master

NumberT1T2T3Y/NRESULT
5645X XYX
3241 XXY 
7592   N 
1643  XY 
2549X  Y 
4759   N 
6247XX YX
8713   N 
9437 X N 

 

Issue

IDSTATUSREF_NUMCATEGORYTYPE
23OPENMISSINGSPICYBURGER
11OPEN1643/2957SWEETCHOCLATE
5CLOSED2549SALTYSALT
10OPEN3241/1643SWEETCANDY
8OPEN7592/6247SWEETCAKE;#CANDY
4CLOSED4759SPICYGRAVY
12OPEN5645SWEETICE;#CAKE
15CLOSEDMISSINGSPICYBURGER
17OPEN8713SWEETCAKE

 

Thanks for your help in advance ğŸ˜Š

 

BR

Mechi ğŸ”§

Need Help
  • Mechi's avatar
    Mechi
    6 years ago

    Hello Anonymous , Anonymous ,

     

    Thank you guys for your  good support in sharing the knowledge with your codes ğŸ˜Š

     

    I had slightly refined the codes from your inputs and finally cracked it with simple code

     

    ref. snap

     

    Updated Code:-

     

    let fresult
    = if ([#"Y/N"] = "Y") then
    let result = if
    (Table.RowCount(Table.SelectRows
    (Table.FindText(#"Issue",[Number]),
    each [STATUS] = "OPEN" and
    [CATEGORY] = "SWEET" and
    Text.Contains([TYPE], "CAKE")))) > 0 then "X" else ""
    in result
    else ""

    in fresult

     

    - Converted the Number format to Text format inside Edit Queries

    - I had started filtering with Y/N values and then counted the table rows based on the filters of OPEN, SWEET, CAKE

    - Finally if the count was more than Zero, then i had published my results as "X" 

     

    BR

    Mechi ğŸ”§

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This query is the issue broken out to prepare for an inner join merge

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBBDoMgEEXvQrdNLMMgmq4oTizRqhHbxhjvf42CNgG76OpPyOMNn2VhINiZ9QN1Ph7WOdvVfnKDNbPP23OsaWTreWGcR5DnKDIopQrom2jyae69afVEGyzDQds7qvwAEssA6naav7kbL9EoAHkWtKlRd9W8kUUElSwhywEPq3VD11PEMd2O/kbSqB71a6c4RKvMUSZCazZfs5fhhzb/P0lFZ6G4+HkkW9cP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, STATUS = _t, REF_NUM = _t, CATEGORY = _t, TYPE = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"REF_NUM", Splitter.SplitTextByDelimiter("/", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "REF_NUM"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"TYPE", Splitter.SplitTextByDelimiter(";#", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "TYPE"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter1", each ([STATUS] = "OPEN") and ([CATEGORY] = "SWEET") and ([TYPE] = "CAKE"))
in
    #"Filtered Rows"

     This query is the master merged and filtered

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1BCsAwCATAv3jOJbpq84meW0L+/42GpmghoAjDsvZOalAqdM39zk2jdBJGTUt3bbx8zflqNUhQhlnRsjwYs2TvMIb/3kX68Cp7ukE83y0eDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, T1 = _t, T2 = _t, T3 = _t, #"Y/N" = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Number"}, Issue, {"REF_NUM"}, "Issue", JoinKind.Inner),
    #"Filtered Rows" = Table.SelectRows(#"Merged Queries", each ([#"Y/N"] = "Y")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Issue"})
in
    #"Removed Columns"

    Regards,

    Mike

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Revised Master to match your result

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1BCsAwCATAv3jOJbpq84meW0L+/42GpmghoAjDsvZOalAqdM39zk2jdBJGTUt3bbx8zflqNUhQhlnRsjwYs2TvMIb/3kX68Cp7ukE83y0eDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, T1 = _t, T2 = _t, T3 = _t, #"Y/N" = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Number"}, Issue, {"REF_NUM"}, "Issue", JoinKind.LeftOuter),
    AddResult = Table.AddColumn(#"Merged Queries", "Result", each if Table.RowCount([Issue]) > 0 and [#"Y/N"] = "Y" then "X" else null),
    #"Removed Columns" = Table.RemoveColumns(AddResult,{"Issue"})
in
    #"Removed Columns"
      • Mechi's avatar
        Mechi
        Icon for Helper I rankHelper I
        6 years ago

        Hello

         Anonymous Thanks for the code

         In the code there are few things which hasn't been considered

         The columns from Table Issue [STATUS]="OPEN", [CATEGORY]="SWEET", [TYPE]="CAKE" hasn't been filtered

         I had expected the results of "X" only for [Number] = 5645, 6247, as per your code it gives result only on 5645, 2549 because it has not been filtered for the columns in the Table Issue mentioned above

         

        In Merged queries you had used   "JoinKind.LeftOuter" that's why it identifies 5645, 2549 but i needs the code to search for the Number from Master to Issue REF_NUM because the strings in REF_NUM can add many strings (e.g. 7592/6247/2549/5645/6247/.../..../..../)

         

        If you can slightly modify the code it will be helpful 

         

        BR

        Mechi ğŸ”§