Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have the following table:
ISSUE ID | MONTH | STATUS |
1 | 1/1/2019 | Open |
2 | 1/1/2019 | Closed |
3 | 1/1/2019 | WIP |
1 | 2/1/2019 | WIP |
2 | 2/1/2019 | Closed |
3 | 2/1/2019 | Closed |
I want to add a new column in Power Query called Status Changed (Y/N) which should have the following values:
ISSUE ID | MONTH | STATUS | Status Changed (Y/N) |
1 | 1/1/2019 | Open | N |
2 | 1/1/2019 | Closed | N |
3 | 1/1/2019 | WIP | N |
1 | 2/1/2019 | WIP | Y |
2 | 2/1/2019 | Closed | N |
3 | 2/1/2019 | Closed | Y |
So, I have three issue IDs 1, 2 and 3. There are records in the table for the months of Jan and Feb. As you can see, the status of Issues 1 and 3 changed from Jan to Feb. My new column should flag these as "Y" meaning that there has been a change in the status from the previous month.
How can I achieve this in Power Query?
Thanks in advance.
Solved! Go to Solution.
Try this.
Please see attached file's Query Editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjC0BDL9C1LzlGJ1opWMUMWdc/KLU1PAMsaoMuGeAWBhkEFGmMJGqMJo5mCRiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ISSUE ID" = _t, MONTH = _t, STATUS = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ISSUE ID", Int64.Type}, {"MONTH", type date}, {"STATUS", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ISSUE ID", Order.Ascending}, {"MONTH", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ISSUE ID"}, {{"ALL", each Table.AddIndexColumn(_,"Index",1,1), type table }}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let mytable=[ALL] in Table.AddColumn([ALL],"Status Changed",each let previousstatus=[Index]-2 in try (if [STATUS]=mytable[STATUS]{previousstatus} then "N" else "Y") otherwise "N")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ALL"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"MONTH", "STATUS", "Index", "Status Changed"}, {"MONTH", "STATUS", "Index", "Status Changed"}) in #"Expanded Custom"
Try this.
Please see attached file's Query Editor for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjC0BDL9C1LzlGJ1opWMUMWdc/KLU1PAMsaoMuGeAWBhkEFGmMJGqMJo5mCRiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ISSUE ID" = _t, MONTH = _t, STATUS = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ISSUE ID", Int64.Type}, {"MONTH", type date}, {"STATUS", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ISSUE ID", Order.Ascending}, {"MONTH", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ISSUE ID"}, {{"ALL", each Table.AddIndexColumn(_,"Index",1,1), type table }}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let mytable=[ALL] in Table.AddColumn([ALL],"Status Changed",each let previousstatus=[Index]-2 in try (if [STATUS]=mytable[STATUS]{previousstatus} then "N" else "Y") otherwise "N")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ALL"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"MONTH", "STATUS", "Index", "Status Changed"}, {"MONTH", "STATUS", "Index", "Status Changed"}) in #"Expanded Custom"
Hi @Zubair_Muhammad ,
I need one more help please - how can I add more conditions to the Custom column? I need to have two conditions like below:
Table.AddColumn(#"Grouped Rows", "Custom", each let mytable=[ALL] in
Table.AddColumn([ALL],"Status Changed",each let previousstatus=[Index]-2 in try (if [Risk]=mytable[Risk]{previousstatus} or [Status]=mytable[Status]{previousstatus} then "N" else "Y") otherwise "N"))
Syntax-wise, this is correct but it doesn't give me the correct results. Below is a sample dataset for your reference:
Controversy ID | Date | Project Name | Risk | Status | Index | Status Changed |
15 | 7/1/2019 | ACWA Power Barka Services 1 (Mauritius) | Medium | closed | 1 | N |
15 | 8/1/2019 | ACWA Power Barka Services 1 (Mauritius) | Low | closed | 2 | N |
The "Status Changed" column should be Y in this case as well.
So basically, the "Status Changed" column should show Y if either Risk has changed or Status has changed.
Thank you for your response! Your solution works perfectly for this specific case. However, basing the conditional check on index number - 2 would not be the most efficient way since we don't know how many records would be present between the current month and previous month for the particular issue ID.
For instance, the sample table that I have shared could also be like this:
ISSUE ID | MONTH | STATUS | Status Changed (Y/N) |
1 | 1/1/2019 | Open | N |
2 | 1/1/2019 | Closed | N |
3 | 1/1/2019 | WIP | N |
1 | 2/1/2019 | WIP | Y |
3 | 2/1/2019 | WIP | N |
So basically, there may not be a record for the Issue ID 2 for the month of February at all (since it was already closed in Jan. Additionally, we still have a record for Issue ID 3 for Feb but its status has not changed (possibly because the team hasn't gotten to it yet). Therefore, we would see an "N" in our Status Changed (Y/N) column.
Thus, is it possible to make the check based on the date/month directly?
If [Table][Previous_Month][Status] = [Table][Current_Month][Status]
then Status_Changed = N
Else Status_Changed = Y
The formula I gave you works with the new sample as well
Could you give me a sample data where it doesn't work, So I can try to fix it
ISSUE ID | MONTH | STATUS | Status Changed (Y/N) |
1 | 1/1/2019 | Open | N |
2 | 1/1/2019 | Closed | N |
3 | 1/1/2019 | WIP | N |
1 | 2/1/2019 | WIP | Y |
3 | 2/1/2019 | WIP | N |
I wasn't replicating the steps exactly as your PBIX file. That's why I was getting incorrect results. But, the solution you have given works for all cases. Thank you!
Would you recommend any sources for brushing up Power/M Query skills?
I am so sorry for late reply.
Following books are fantastic for learning PQ.
Also, subscribe to following blogs
https://www.thebiccountant.com/
https://www.excelguru.ca/blog/