Power Query Custom Column to identify First Occurrence of Data
Hi All,
I am looking for help in identifying the First Occurrence of Customer ID in my data table.
I want to build a custom column to mark all first occurrence as "1" and others as "0"
We need to compare the Date_Time column to see which row qualify's first as there will be several duplicate records for each day.
Any Ideas appreciated.
You could create a query that groups the customer and calculates the min of the Date_Time, then join that query to your original using customer id and date_time. Where it's not null, it's the first instance.
Everyone who confuses correlation and causation ends up dead.
i read about a function named "Occurrence.first", but there is no documentation on how to use it. Do you have any idea about this function in Power Query ?
Thanks for the Idea.
Occurrence.First is a parameter, not a function. You'd use it as an argument to something like Text.PositionOf
How can I use List. Min to find the earliest time from the table for each record matching the current row Customer ID
Since you appear to simply ignore my suggestions, I'll leave you in others' capable hands.
while I approved your comment, I am exploring alternate ways to reach the same result without needing to create multiple queries and merge.
I thought its easier to do it with couple of custom columns if I am not wrong:
1. Create a conditional custom column where we identify the earliest occurrence Date_Time for each Customer ID; &
2. Them use another custom column to match the current row Date_Time to previous custom column if Matched 1 & otherwise 0
Think more like db.
rory's method is probably the easiest to implement and maintain.
I haven't tested the speed difference. But I'd imaging using List functions can be slower than self join method. As it requires evaluation at each row of the table.
Using custom function method...
fnMinDate
This is then invoked in original table by adding custom column with following...
Please Login or Register to view this content.
Where #"Changed Type" should be replaced with step name that's immediately before adding custom column.Please Login or Register to view this content.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
Hi All,
I am opening this thread again as the way I designed the query consumes a lot of time to process.
I have a small database of around 300,000 rows only. But the query takes 25 minutes to process.
Please see my codes from the Advanced Query Editor (is there a better way to do this ?):
Please Login or Register to view this content.
Last edited by ibuhary; 12-03-2019 at 04:26 AM. Reason: to conform forum rules
Administrative Note:
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA or M Code formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Its done, I wrap the codes inside""Please Login or Register to view this content.
What sort of cardinality do you have in your #"Passenger phone number" field? Tens, hundreds, thousands...?
Another way of asking: what's the average number of rows per #"Passenger phone number"
It will almost certainly be faster to do this using DAX as a calculated column in your data model, rather than in M (power query).
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
You could try creating partitions, ordering by #"Completed Time" and adding an Index, to indicate the occurrence:
Please Login or Register to view this content.
I sent you a private chat. As this thread is Solved.
Please don't send private messages - please keep the discussion in this thread (per forum rules) so that others may participate and benefit.Originally Posted by ibuhary
where can I insert this code, at the beginning ?
inside the "Advanced Editor" which looks like this now (I am referencing another query and starting fresh here):
let
Source = fData
in
Source
Last edited by ibuhary; 12-16-2019 at 08:17 AM. Reason: typo error
So your query could be:
Please Login or Register to view this content.
Thanks a lot. I hope it will come good. Let me Test it, and I will mark the post as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks