Adding Calculated field in Power Query

[Anonymous]

Hi,

Hope someone can help.

I have data as in the table below.

ID	Start Date	End Date	Status	Reason
1	01/04/2019	30/04/2019	A	R1
1	30/04/2019	01/10/2020	B	R6
1	01/10/2020		A
2	02/03/2020	04/05/2021	B	R2
2	04/05/2021		B
3	02/02/2019	06/03/2020	C	R1
3	06/03/2020	08/09/2020	B	R2
3	08/09/2020	03/02/2021	A	R2
3	03/02/2021	12/06/2021	A	R4

I want to add a calculated field, in Power Query, which should be 'Yes' for each ID if reason, R1 has been selected in atleast one of the rows and 'No' otherwise. Please see required table below.

ID	Start Date	End Date	Status	Reason	CalcCol
1	01/04/2019	30/04/2019	A	R1	Yes
1	30/04/2019	01/10/2020	B	R6	Yes
1	01/10/2020		A		Yes
2	02/03/2020	04/05/2021	B	R2	No
2	04/05/2021		B		No
3	02/02/2019	06/03/2020	C	R1	Yes
3	06/03/2020	08/09/2020	B	R2	Yes
3	08/09/2020	03/02/2021	A	R2	Yes
3	03/02/2021	12/06/2021	A	R4	Yes

Is this possible?

Many Thanks in advance.

[mahoneypat · Accepted solution]

This kind of thing is better done with DAX, but here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5BDsMgDAS/YvkcibVJUHNM+4NeI/7/jRhCg90Dh9WMlz1PFl4YkrAmhewWKMOlw95XuC63GRjZncCSwtK7meUxA6PRRB1rw5qQH2yd2FqSX5FO0zMa/C7Ko0jnouJqP3N7N0v48pWwh+06Tc/IjvoPfcDxZ3pGYltKMFeu9QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start Date" = _t, #"End Date" = _t, Status = _t, Reason = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let 
thisID = [ID],
thisIDlist = Table.SelectRows(Source, each [ID] = thisID)[Reason]
in 
if List.Contains(thisIDlist, "R1") then "Yes" else "No")
in
    #"Added Custom"

Pat

[Anonymous]

Many thanks @mahoneypat 

[Anonymous]

Thanks @wdx223_Daniel and @Syndicate_Admin for your quick response. Sorry, I should have mentioned I was trying to do this in Power Query.

[wdx223_Daniel]

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5BDsMgDAS/YvkcibVJUHNM+4NeI/7/jRhCg90Dh9WMlz1PFl4YkrAmhewWKMOlw95XuC63GRjZncCSwtK7meUxA6PRRB1rw5qQH2yd2FqSX5FO0zMa/C7Ko0jnouJqP3N7N0v48pWwh+06Tc/IjvoPfcDxZ3pGYltKMFeu9QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start Date" = _t, #"End Date" = _t, Status = _t, Reason = _t]),
Custom1 = Table.Buffer(Table.Group(Source,"ID",{"n",each List.Contains([Reason],"R1")})),
Custom2 = Table.AddColumn(Source,"CalcCol",each Custom1{[ID=[ID]]}?[n]?)
in
Custom2

[Syndicate_Admin]

[wdx223_Daniel]