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.
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
Solved! Go to 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"
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
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
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
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.