Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | N | ||||
1643 | X | Y | |||
2549 | X | Y | |||
4759 | N | ||||
6247 | X | X | Y | X | |
8713 | N | ||||
9437 | X | N |
Issue
ID | STATUS | REF_NUM | CATEGORY | TYPE |
23 | OPEN | MISSING | SPICY | BURGER |
11 | OPEN | 1643/2957 | SWEET | CHOCLATE |
5 | CLOSED | 2549 | SALTY | SALT |
10 | OPEN | 3241/1643 | SWEET | CANDY |
8 | OPEN | 7592/6247 | SWEET | CAKE;#CANDY |
4 | CLOSED | 4759 | SPICY | GRAVY |
12 | OPEN | 5645 | SWEET | ICE;#CAKE |
15 | CLOSED | MISSING | SPICY | BURGER |
17 | OPEN | 8713 | SWEET | CAKE |
Thanks for your help in advance 😊
BR
Mechi 🔧
Solved! Go to 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
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 🔧
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
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
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 🔧
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 🔧
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:
Hope this is what you needed :).
Kind regards,
JB
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.