cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate III
Advocate III

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:

NameSubject
JohnInvatation to my Dave's Birthday party
DaveQuestion
MikeInvatation to my Dave's Birthday party
MikeCome with me to Woodstock

 

Crossjoin Event-Relation table with the desired Is_Invited column.

                      

Event       NameIs_Invited
WoodstockJohnfalse
WoodstockDavefalse
WoodstockMiketrue
Dave's Birthday party Johntrue
Dave's Birthday party Davefalse
Dave's Birthday party Miketrue

 

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

1 ACCEPTED SOLUTION
Super User II
Super User II

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
Super User II
Super User II

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors