Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
42 | |
35 | |
27 | |
18 | |
17 |