Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks @wdx223_Daniel and @Syndicate_Admin for your quick response. Sorry, I should have mentioned I was trying to do this in Power Query.
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