Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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:
ID | DATE | TEXT |
001 | 1/12/2022 | A |
001 | 1/13/2022 | A |
001 | 1/15/2022 | B |
002 | 1/12/2022 | C |
003 | 1/12/2022 | A |
003 | 1/13/2022 | B |
003 | 1/13/2022 | C |
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!
Solved! Go to Solution.
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.
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.
@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!
@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.
@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?
@klili is this something you are looking for?. I added a record for id 4 to test a corner case:
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.
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!
User | Count |
---|---|
94 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
146 | |
112 | |
73 | |
56 |