Is there a way to identify the 1st occurrence of a value in a new column in Power Query with respect to "date"? For example,
Date Value Unique 6/10/16 a 1 6/10/16 b 1 6/10/16 c 1 6/10/16 c 0 6/10/16 a 0 6/11/16 a 1
I have been able to create a new column that gives a count of the times the value is duplicated. But I can't get quite what I need.
Hi,
Can you sort on the first two columns, or do you need to preserve the current order of rows?
Don
Please remember to mark your thread 'Solved' when appropriate.
I believe sorting on those rows will be fine. The pivot table I'll eventually be using will group by date anyways. I can always try whatever you're thinking and see if it'll work out! Thanks!
Here are the steps you will require:
1. Sort on date.
2. Sort on value.
3. Add an index column, then rename this step Sorted.
4. Add a custom column called Unique, using the following formula:
=if [Index]>0 then if Sorted{[Index] - 1}[Date] = [Date] and Sorted{[Index] - 1}[Value] = [Value] then 0 else 1 else 1
You may then remove the Index column if you wish.
Last edited by xlnitwit; 07-13-2016 at 05:05 PM. Reason: Correct step name
Oh, how clever! I'll test this out! Thanks!
Edit: Works wonderfully! Thanks so much!
Last edited by kimbekaw; 07-14-2016 at 12:00 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks