Nested Text.Contains based on "higher" column

[Anonymous]

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

[ImkeF · Accepted solution]

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"

[Anonymous]

Hi Imke,

Thanks, that did the trick! Query is taking a while indeed, I will look for more efficient methods in the future. 

Thank you for your time!

Koen