Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mechi
Helper I
Helper I

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 Compare.jpg

 

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 🔧

1 ACCEPTED SOLUTION

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

Result.jpg

 

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 🔧

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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
Not applicable

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"

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

Result.jpg

 

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
Not applicable

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"

  

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

Result.jpg

 

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 🔧

Anonymous
Not applicable

Hi @Mechi 

 

The main table - "Issue" filtering happening in the fFilter step, you can modify the conditions if neccesary:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ivNTUotUtJRCjEEEUYgwhhIROr7Ackg1+BQnxClWJ1oJVMzE1OgSAQQQ6lIMA2SMzYyMUSIw+TAUuamlkYQKQjyg0kYmpkYw0VRtBiZmlgibEKWMQGahtUwMyMTcyTbURxnYW5ojFWTpYmxOcJ6uEwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t, #"Column 6" = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Column 1", type text}, {"Column 2", type text}, {"Column 3", type text}, {"Column 4", type text}, {"Column 5", type text}, {"Column 6", type text}}),
  #"Promoted headers" = Table.PromoteHeaders(#"Changed column type", [PromoteAllScalars = true]),
  #"Changed column type 1" = Table.TransformColumnTypes(#"Promoted headers", {{"Number", Int64.Type}, {"T1", type text}, {"T2", type text}, {"T3", type text}, {"Y/N", type text}, {"RESULT", type text}}),
  #"Renamed columns" = Table.RenameColumns(#"Changed column type 1", {{"RESULT", "EXPECTED RESULT"}}),
  fFilter = (pTable as table)=> Table.RowCount(Table.SelectRows(pTable, each [STATUS] = "OPEN" and [CATEGORY] = "SWEET" and Text.Contains([TYPE], "CAKE"))),
  mMaster = #"Renamed columns",
  mIssue = let 
      //Parse multiple values in the REF column and filter out "missing"
      Input = Issue,
      #"Inserted lowercased text" = Table.AddColumn(Input, "reference", each Text.Split([REF_NUM], "/"), type list),
      #"Expanded reference" = Table.ExpandListColumn(#"Inserted lowercased text", "reference"),
      #"Changed column type 2" = Table.TransformColumnTypes(#"Expanded reference", {{"reference", Int64.Type}}),
      #"Removed errors" = Table.RemoveRowsWithErrors(#"Changed column type 2", {"reference"})
    in #"Removed errors",
  #"Merge mIssue" = Table.NestedJoin(mMaster, {"Number"}, mIssue, {"reference"}, "mIssue", JoinKind.LeftOuter),
  #"Add Result" = Table.AddColumn(#"Merge mIssue", "RESULT", each if [#"Y/N"] = "N" then "" else if fFilter([mIssue]) = 0 then "" else "X", type text),
  CleanUp = Table.RemoveColumns(#"Add Result", {"mIssue"})
in
  CleanUp

 

(just in case) the Issue table:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDNDoMgDIDfhV2XOBBEsxPTzhF/A7iFGLP3f4sBLgF32Kml+fq1ZV2RbNAZaSPMol2i4P4el8FltTDQTsq61NgZ0HZeEcnda5phdGGQWsux9c2zrD12W1QLKoAYRxAXNM9IxbhHXwDGyx9T3bsBAWa+0E8a/CaE0cqDojf2G3fjJRpzQnHmtalRjI0NZBlBziqSFYQeRosOrqeI03Q6dR3JRa0Sz53CJFpZQVkilHXwdfsx+HDN/0/i0VlynP8sibbtAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t]),
  #"Promoted headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"ID", Int64.Type}, {"STATUS", type text}, {"REF_NUM", type text}, {"CATEGORY", type text}, {"TYPE", type text}})
in
  #"Changed column type"

 

The out put of Main:

pbi.png

 

Hope this is what you needed :).

Kind regards,

JB

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors