cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ptewary Frequent Visitor
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

Accepted Solutions
Super User
Super User

Re: Comparing row values in Power Query

@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"
6 REPLIES 6
Super User
Super User

Re: Comparing row values in Power Query

@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"
ptewary Frequent Visitor
Frequent Visitor

Re: Comparing row values in Power Query

@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

Super User
Super User

Re: Comparing row values in Power Query

@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
ptewary Frequent Visitor
Frequent Visitor

Re: Comparing row values in Power Query

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?

Super User
Super User

Re: Comparing row values in Power Query

@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

 

 

 

 

ptewary Frequent Visitor
Frequent Visitor

Re: Comparing row values in Power Query

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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 340 members 4,180 guests
Please welcome our newest community members: