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 Number T1 T2 T3 Y/N RESULT 5645 X   X Y X 3241   X X Y   7592       ...
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 ðŸ”§

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 ðŸ”§

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Mechi,

I performed the filtering within the Issue query. Also in the issue query, I broke the refnum into multiple records so that many strings would be searched. Is the requirement that everything be done in one query? If so, then I offer the revised code below (which also filters on the X):

 

let 
  #"Split Issue by Ref" = Table.ExpandListColumn(Table.TransformColumns(Issue, {{"REF_NUM", Splitter.SplitTextByDelimiter("/", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "REF_NUM"),
    #"Split by Type" = Table.ExpandListColumn(Table.TransformColumns(#"Split Issue by Ref", {{"TYPE", Splitter.SplitTextByDelimiter(";#", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "TYPE"),
    #"Filter Issue" = Table.SelectRows(#"Split by Type", each ([STATUS] = "OPEN") and ([CATEGORY] = "SWEET") and ([TYPE] = "CAKE")),
   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"},  #"Filter Issue" , {"REF_NUM"}, "Issue", JoinKind.Inner),
    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"}),
    #"Leave only X" = Table.SelectRows(#"Removed Columns", each ([Result] = "X"))
in
    #"Leave only X"