Forum Discussion
Find Value in one table with Text field in another table with Filters
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 🔧
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
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 🔧