Forum Discussion
Help with query for evaluating a condition between two tables involving multiple columns
Hi All,
I have a column which consists of ticket created date and another column with a ticket creator ID in Table A. Table B has three fields which consists of ticket creator ID, ticket creator's date of joining and date of leaving.
Could someone help me find a solution in M query to look up ticket creator ID from Table A against Table B, followed by establishing if the ticket created date is between ticket creator's date of joining and date of leaving.
If true, return yes else return no in a custom column. I have achieved this in DAX but am keen on doing the same using a query in M.
Any suggestions would be appreciated, thanks in advance.
6 Replies
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523HI lbendlin ,
Please see the sample data as follows:
Table A
Ticket created Ticket solved Submitter ID 2022-07-09T00:47:03 2022-07-09T00:47:30 123456789101 2022-07-09T00:47:03 2022-07-09T00:47:30 112131415161 2022-07-09T00:47:03 2022-07-09T00:47:30 718192021222 Table B
Submitter name DOJ DOL Submitter ID User A 01/01/01 12/31/99 123456789101 User B 01/01/01 08/28/20 112131415161 I require a custom column in Table A to return a Yes / No or 0 / 1 depending on the following conditions to match:
1) Submitter ID in Table A exists in Table B
2) 'Ticket created' for this submitter ID in Table A should be in between DOJ and DOL dates in Table B
and would like to achive the above using M query if at all possible.
Expected Outcome in Table A
Ticket created Ticket solved Submitter ID CustomColumn 2022-07-09T00:47:03 2022-07-09T00:47:30 123456789101 Yes 2022-07-09T00:47:03 2022-07-09T00:47:30 112131415161 No 2022-07-09T00:47:03 2022-07-09T00:47:30 718192021222 No Any help would be appreciated.
Table A:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lcuxDQAhEAPBXi4GyfYBB9TxGaL/NiD/iHRWu5YJUkZkjA+YJSbc0l8dVykvtUUfBG2nx5mis7Cyvc/BznETJdneBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket created" = _t, #"Ticket solved" = _t, #"Submitter ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket created", type datetime}, {"Ticket solved", type datetime}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (k)=> if Table.RowCount(Table.SelectRows(#"Table B",each k[Submitter ID]=[Submitter ID] and k[Ticket created]>DateTime.From([DOJ]) and k[Ticket created]<DateTime.From([DOL]) )) > 0 then "Yes" else "No") in #"Added Custom"Table B:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVJwVNJRMjDUByMg09BI39hQ38gIzDQ2MTUzt7A0BMrE6kDVO6GqN7DQNwIiA5B6QyNDY0MTQ1NDM6D6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Submitter name" = _t, DOJ = _t, DOL = _t, #"Submitter ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DOJ", type date}, {"DOL", type date}}) in #"Changed Type"Note: "12/31/99" is a VERY bad choice for a dummy date. Better leave it blank.
Hi lbendlin ,
Many thanks for your help. I tried to use the formula to add a custom column and it created a column with clickable text mentioning "Function"
function (k as any) as any.
Regarding the use of 12/31/99 (31-12-2099), if I choose to use blanks, Power BI will report errors for blanks rows with the data type set to date. Pls advise.
Thank you.
Show your Power Query code. Probably a typo somewhere.
Use COALESCE([Date],TODAY()) to handle blanks.
I am using the code for the custom column referencing the same sample data shared earlier. However my original project has multiple other columns too. The code given as solution has been used in the very end. The "each" before (k)=> gets inserted automatically if that is causing the issue.
let
Source = Excel.Workbook(File.Contents("C:\Users\asah\Documents\Power BI\Projects\Table_A.xlsx"), null, true),
#"Table_A_Sheet" = Source{[Item="Table_A",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Table_A_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ticket created - Timestamp", type datetime}, {"Ticket solved - Timestamp", type datetime}, {"Ticket ID", Int64.Type}, {"Ticket priority", type text}, {"Site Number", type text}, {"Site Number (Obsolete)", type text}, {"Name", type text}, {"Type", type text}, {"Type of issue", type text}, {"Ticket subject", type text}, {"Ticket status", type text}, {"Ticket group", type text}, {"Impact", type text}, {"Type of issue - country", type text}, {"Submitter name", type text}, {"Submitter ID", Int64.Type}, {"Assignee name", type text}, {"Ticket type", type text}, {"Tick here if incident is linked to a problem", type logical}, {"Ticket tags", type text}, {"Code department", type text}, {"Code type", type text}, {"Sub-Tasks", type text}, {"First resolution time (min)", Int64.Type}, {"Full resolution time (min)", Int64.Type}, {"First assignment time (min)", Int64.Type}, {"Average Group stations", Int64.Type}, {"zd-Tickets in SLA", Int64.Type}, {"out(SLA)", Int64.Type}, {"zd-Tickets without SLA", Int64.Type}, {"Good satisfaction tickets", Int64.Type}, {"Bad satisfaction tickets", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Ticket ID"}, {{"MyTable", each _, type table [#"Ticket created - Timestamp"=nullable datetime, #"Ticket solved - Timestamp"=nullable datetime, Ticket ID=nullable number, Ticket priority=nullable text, Site Number=nullable text, #"Site Number (Obsolete)"=nullable text, Name=nullable text, Type=nullable text, Type of issue=nullable text, Ticket subject=nullable text, Ticket status=nullable text, Ticket group=nullable text, Impact=nullable text, #"Type of issue - country"=nullable text, Submitter name=nullable text, Submitter ID=nullable number, Assignee name=nullable text, Ticket type=nullable text, Tick here if incident is linked to a problem=nullable logical, Ticket tags=nullable text, Code department=nullable text, Code type=nullable text, #"Sub-Tasks"=nullable text, #"First resolution time (min)"=nullable number, #"Full resolution time (min)"=nullable number, #"First assignment time (min)"=nullable number, Average Group stations=nullable number, #"zd-Tickets in SLA"=nullable number, #"out(SLA)"=nullable number, #"zd-Tickets without SLA"=nullable number, Good satisfaction tickets=nullable number, Bad satisfaction tickets=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "TicketTag", each Table.Column([MyTable],"Ticket tags")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Ticket ID"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"TicketTag", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Expanded MyTable" = Table.ExpandTableColumn(#"Extracted Values", "MyTable", {"Ticket created - Timestamp", "Ticket solved - Timestamp", "Ticket ID", "Ticket priority", "Site Number", "Site Number (Obsolete)", "Name", "Type", "Type of issue", "Ticket subject", "Ticket status", "Ticket group", "Impact", "Type of issue - country", "Submitter name", "Submitter ID", "Assignee name", "Ticket type", "Tick here if incident is linked to a problem", "Code department", "Code type", "Sub-Tasks", "First resolution time (min)", "Full resolution time (min)", "First assignment time (min)", "Average Group stations", "zd-Tickets in SLA", "out(SLA)", "zd-Tickets without SLA", "Good satisfaction tickets", "Bad satisfaction tickets"}, {"Ticket created - Timestamp", "Ticket solved - Timestamp", "Ticket ID", "Ticket priority", "Site Number", "Site Number (Obsolete)", "Name", "Type", "Type of issue", "Ticket subject", "Ticket status", "Ticket group", "Impact", "Type of issue - country", "Submitter name", "Submitter ID", "Assignee name", "Ticket type", "Tick here if incident is linked to a problem", "Code department", "Code type", "Sub-Tasks", "First resolution time (min)", "Full resolution time (min)", "First assignment time (min)", "Average Group stations", "zd-Tickets in SLA", "out(SLA)", "zd-Tickets without SLA", "Good satisfaction tickets", "Bad satisfaction tickets"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded MyTable", {"Ticket ID"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Ticket group] <> "111_SD" and [Ticket group] <> "MTB")),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "SiteNumber", each if [Site Number] = "" then [#"Site Number (Obsolete)"] else [Site Number]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Ticket created - Timestamp", "Ticket solved - Timestamp", "Ticket ID", "Ticket priority", "Site Number", "Site Number (Obsolete)", "SiteNumber", "Name", "Type", "Type of issue", "Ticket subject", "Ticket status", "Ticket group", "Impact", "Type of issue - country", "Submitter name", "Submitter ID", "Assignee name", "Ticket type", "Tick here if incident is linked to a problem", "Code department", "Code type", "Sub-Tasks", "First resolution time (min)", "Full resolution time (min)", "First assignment time (min)", "Average Group stations", "zd-Tickets in SLA", "out(SLA)", "zd-Tickets without SLA", "Good satisfaction tickets", "Bad satisfaction tickets", "TicketTag"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Site Number", "Site Number (Obsolete)"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Ticket created - Timestamp", "Ticket created"}, {"Ticket solved - Timestamp", "Ticket solved"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each (k)=> if Table.RowCount(Table.SelectRows(#"Table B",each k[Submitter ID]=[Submitter ID] and k[Ticket created]>DateTime.From([DOJ]) and k[Ticket created]<DateTime.From([DOL]) )) > 0 then "Yes" else "No")
in
#"Added Custom1"
