Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ptewary
Frequent Visitor

Comparing row values in Power Query

Hi,

 

I have the following table:

 

ISSUE IDMONTHSTATUS
11/1/2019Open
21/1/2019Closed
31/1/2019WIP
12/1/2019WIP
22/1/2019Closed
32/1/2019Closed

 

I want to add a new column in Power Query called Status Changed (Y/N) which should have the following values:

 

ISSUE IDMONTHSTATUSStatus Changed (Y/N)
11/1/2019OpenN
21/1/2019ClosedN
31/1/2019WIPN
12/1/2019WIPY
22/1/2019ClosedN
32/1/2019ClosedY

 

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.

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@ptewary 

 

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"

Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

@ptewary 

 

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"

Regards
Zubair

Please try my custom visuals

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 IDDateProject NameRiskStatusIndexStatus Changed
157/1/2019ACWA Power Barka Services 1 (Mauritius)Mediumclosed1N
158/1/2019ACWA Power Barka Services 1 (Mauritius)Lowclosed2N

The "Status Changed" column should be in this case as well.

 

So basically, the "Status Changed" column should show if either Risk has changed or Status has changed.

@Zubair_Muhammad 

 

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 IDMONTHSTATUSStatus Changed (Y/N)
11/1/2019OpenN
21/1/2019ClosedN
31/1/2019WIPN
12/1/2019WIPY
32/1/2019WIPN

 

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

@ptewary 

 

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

Regards
Zubair

Please try my custom visuals

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?

@ptewary 

 

I am so sorry for late reply.

Following books are fantastic for learning PQ.

Also, subscribe to following blogs

 

https://www.thebiccountant.com/

https://blog.crossjoin.co.uk

https://www.excelguru.ca/blog/

 

PQ books.png

 

 

 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors