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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
klili
New Member

Compare text value row data in PBI

I have a dataset that has been built that includes version history. Some items have multiple versions, while newer ones only have a single row. The data get refreshed weekly. 

 

I am wanting to compare the last 2 dates for each item (if there are multiple entries) to see if a value changed. 

 

I can't share the actual data but it looks something like this:

IDDATETEXT
0011/12/2022A
0011/13/2022A
0011/15/2022B
0021/12/2022C
0031/12/2022A
0031/13/2022B
0031/13/2022C

 

I found this post : https://community.powerbi.com/t5/Desktop/Difference-between-two-rows

which when I was testing with a single ID worked, but I ran into issues with the multiple IDs in a table. 

 

Conceptually I know I need something along the lines of if the ID is the same, using the last 2 dates, compare the TEXT field. 

 

Thanks in advance for your help!

1 ACCEPTED SOLUTION
klili
New Member

Managed to solve it by creating a Partition and geneating an index column for each ID, the expanding the partition, creating a calculation column that increases the existing index by 1, then merging on the index columns. 

View solution in original post

11 REPLIES 11
klili
New Member

Managed to solve it by creating a Partition and geneating an index column for each ID, the expanding the partition, creating a calculation column that increases the existing index by 1, then merging on the index columns. 

parry2k
Super User
Super User

@klili I'm just asking if the result based on the sample data is correct or not and if you had simply confirmed it, I would have provided you the solution but rather I'm getting all sorts of replies. I hope someone else can help, although I already created the solution I'm having a hard time getting a simple answer. Thanks!!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

What you posted simply wasn't clear to me. More detail as to "is this the result you are looking for" would have helped. because yes, what you sent was correct. I am sorry that a couple replies back and forth is not something you are willing to do when I was confused. I hope you have more patience with others!

parry2k
Super User
Super User

@klili it is your data, what is not clear? It is taking the latest two records of each ids and then comparing the text, not sure what is not clear.

 

ID 1 - latest two records, the text doesn't match

ID 2 - it has only one record, so it shows matched

ID 3 - it took the latest two records,  text doesn't match



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

how did you do it? I cannot confirm that what you did works with my data without being able to apply the results to my data. A screen shot of a table does not confirm that your solution works. Yes, those results look correct. I know what I am asking for is possible - I am asking for how to do it. 

 

parry2k
Super User
Super User

@klili the result that I showed is not what you are looking for? I'm confused. Those are the ids, showing the last two records matched or not. I'm not sure about your reply.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

The screen shot wasn't really very clear. Can you include some more detail so I can see if it solves my question?

parry2k
Super User
Super User

@klili is this something you are looking for?. I added a record for id 4 to test a corner case:

 

parry2k_0-1647268811682.png

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

No, I can add an index to the entire table, but that doesn't help when I need to compare rows with the same ID.

truptis
Community Champion
Community Champion

Hi @klili ,

If you want to find the duplicate enteries then use the below code:
IF_Duplicate_count = IF(CALCULATE(COUNT(tablename[ID]),FILTER(tablename,tablename[ID]=EARLIER(tablename[ID])))>1,1,0)

 

If this IF_Duplicate_count > 1, that means we have duplicate versions.

You can then check the latest dates for the rows having IF_Duplicate_count >1 

you can use MAX(date) function to check the latest date for each ID where IF_Duplicate_count > 1

 

For this, create a column:

Max Date =
CALCULATE (
MAX ( tablename[Date] ),
ALLEXCEPT ( tablename, tablename[ID], filter(tablename,tablename[IF_Duplicate_count] >1)
)

 

Please mark this as a solution if this helps you. Thank you. 

Thanks @truptis  I am not looking for duplicates, I am looking for changes. As I mentioned in my initial question, i need to know if the most recent 2 dates have a TEXT value that has changed. Thanks for the reply!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.