Forum Discussion
Nested Text.Contains based on "higher" column
Hi all,
I have some dificulty with the following case. I hope you can help!
Main goal of the case is to check for each relation if they are invited to event. There are two tables that are relevant to this case: one is a correspandance table and one is a crossjoin table of all events and all relations. Idea was to check if the correspondace table has a row for the relation with the event in the subject, returning true or false.
I thougth that the following PowerQuery would do the trick, but i'm getting a error on the _[Event] part.
Table.AddColumn(#"LastStep", "Is_Invited", each Table.Contains(Table.SelectRows(Correspondance, each Text.Contains([Subject], _[Event])), [Name = _[Name]]) )
Example of the tables:
Correspandance Table:
| Name | Subject |
| John | Invatation to my Dave's Birthday party |
| Dave | Question |
| Mike | Invatation to my Dave's Birthday party |
| Mike | Come with me to Woodstock |
Crossjoin Event-Relation table with the desired Is_Invited column.
| Event | Name | Is_Invited |
| Woodstock | John | false |
| Woodstock | Dave | false |
| Woodstock | Mike | true |
| Dave's Birthday party | John | true |
| Dave's Birthday party | Dave | false |
| Dave's Birthday party | Mike | true |
Hope someone can show me what I'm doing wrong or surgest another sollution. I need the sollution to be in PowerQuery, not Dax, because i want to create a Data flow.
Thanks in advance,
Koen
For performance reasons, you should try to avoid adressing the whole table on a row-by-row-basis. Instead, join (or group) on those attributes who have equality operators and continue from the partitions basically.
The following code is a mockup and the crucial steps are the last 2 where you merge and then add a special column that returns the desired true or falses:
let CorrespondenceTable = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUfLMK0ssSSzJzM9TKMlXyK1UcEksS40pNTAwMi9WcMosKslISaxUKEgsKqlUitWJVgJJA7UFlqYWgzSBxXwzs1PJMAqqzTk/N1WhPLMkQwFIAzWG5+enFJfkJ2crxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Subject = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Subject", type text}}) in #"Changed Type", Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/PTykuyU/OVtJR8srPyANSbo4+wa5KsTqoki6JZak4JX0zs0GSIUGhEDmQ4phSAwMj82IFp8yikoyUxEqFgsSikspDCxAWEascw2oC6lFcEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Event = _t, Name = _t, Is_Invited = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"Name", type text}, {"Is_Invited", type logical}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Event", Text.Trim, type text}}), #"Merged Queries" = Table.NestedJoin(#"Trimmed Text", {"Name"}, Correspondence, {"Name"}, "Correspondence", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "DesiredResult", each List.AnyTrue(List.Transform([Correspondence][Subject], (x) => Text.Contains(x, _[Event])))) in #"Added Custom"
2 Replies
For performance reasons, you should try to avoid adressing the whole table on a row-by-row-basis. Instead, join (or group) on those attributes who have equality operators and continue from the partitions basically.
The following code is a mockup and the crucial steps are the last 2 where you merge and then add a special column that returns the desired true or falses:
let CorrespondenceTable = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUfLMK0ssSSzJzM9TKMlXyK1UcEksS40pNTAwMi9WcMosKslISaxUKEgsKqlUitWJVgJJA7UFlqYWgzSBxXwzs1PJMAqqzTk/N1WhPLMkQwFIAzWG5+enFJfkJ2crxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Subject = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Subject", type text}}) in #"Changed Type", Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/PTykuyU/OVtJR8srPyANSbo4+wa5KsTqoki6JZak4JX0zs0GSIUGhEDmQ4phSAwMj82IFp8yikoyUxEqFgsSikspDCxAWEascw2oC6lFcEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Event = _t, Name = _t, Is_Invited = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"Name", type text}, {"Is_Invited", type logical}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Event", Text.Trim, type text}}), #"Merged Queries" = Table.NestedJoin(#"Trimmed Text", {"Name"}, Correspondence, {"Name"}, "Correspondence", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "DesiredResult", each List.AnyTrue(List.Transform([Correspondence][Subject], (x) => Text.Contains(x, _[Event])))) in #"Added Custom"