Power query add an indicator for the first occurance in a list

Power Query
1

Attached a sample dataset file with the following columns
Dokter; patient; medecine; date ( prescription date for the medecine )
This bulk information is from a external source and saved in a folder as an XLSX file

I was wondering if it is possible in power Query to filter out the first prescription date for a combination patient/drug or at least an additional column with an indication 1 or 0 for the first prescription date
See attached screenshot

Attached the sample set
Thanks in advance for any suggestion
Roger
medecinelist.xlsx (130.4 KB)
print screen drug list

2

Hi @Roger,

See how this works for you. Know that you have 2 lines with an occurrence indicator 1 for med 4.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZjNatxAEIRfxezZgun5lZ7F+GDiPZgQJ4R9fyJyWNU3bIk96lB0T3VVTY/e3i6fv3/ern9fxuX18ufj9nX9vr1E3T9+XT+vP76+ry+x7l855bKktqR+eX+9g7KCVgXlegeNJa0WBEwIJpJimi0UBygv0RSkRyrmRHXZqwqmCKahuXbHrEteXXPARL9jYolhSQALUe6gsuwfAgoFuROVJYOF7ZlKYzrS6itlBTV7pqygcoD63qEjryumJiGPhRSDOqK6WAoUFMnzMFTg6K4KaDOYMlVqDiMnKhPfcB+Y24S5nC3G6W43UlgaYKRNmkvDFhoohBOF0+owM1p3wh3bLEQBddcdDtQEQhKa6+3gLS+529aYdKF1snVRAqgLCXmzJocjso4oNttefTzWPYerYrq6CN0VRJBlmyZqgilhaUBz+dBPmwhHmMBGuSjhw2YdBZSEB7anPPBqOUI1Jr/6JIbJozoQ47FenIS6pUHvo7CDpexwwfoZoU5pdkZayHm8TJeyXrBMLRFDnU4EebudoZ7cEtNGg3xsdrDMuiJqoJOUPJf4bRospJpNdzvh/hLjkTZbCaN9nHb9LO2s6ML6lVknvJHskwRS5/lc8FJI2YG4zTRVQrG0sZDaNfscZqUq3XFAJ0I99JMnS9hrTzdBQmC9/Bgzh2M8w0KcLRlMExF3mcStJyILoWoYtj3Glj5AeEugPXa36Zmsx6cXiAZksa8JyiHrNcGdc1jKCxZ2u0a719ucqtAdNo2C3ckuAMCIVvu0C6JQNoSPyeiecI42rP8ICpkSH6S4L3ElKXfJx7fxeUzrLdRA3anNu7cSM1Kdzp1YbbEZFmJ6Ym/PdeefiWFmNHsWdNMUiP2TSOHeoE+xk1c2MNCddQUvJb52ktMdN1xdixn7PlpxVdhdlU++TYaULGZaB5U5ug9arY/VUM+Cy+XqOkVktaB2cbLzf4OK/p0I3x1lN7SSXaX5FlNTME78snrMtU17g5rPsk0beZ3inXiSxBQQftj5JDbrYP1Pwvs/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dokter = _t, patient = _t, medecine = _t, date = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"date", type date}}),
    AddOccurrence = Table.Combine( Table.Group(ChType, {"patient", "medecine"}, {{"t", each Table.FromColumns( Table.ToColumns( Table.Sort( _,{{"date", Order.Ascending}})) & {{1} & List.Repeat({0}, Table.RowCount(_)-1)}, Table.ColumnNames(ChType) & {"occurrence"}), type table [dokter=nullable text, patient=nullable text, medecine=nullable text, date=nullable date, occurrence=nullable number] }})[t] )
in
    AddOccurrence

With this result

image
image830×278 15.1 KB

I hope this is helpful

4 Likes
3

Melissa

Perfect solution, the indicator 1 for med 4 was a type error.

Is it possible to mail me the file please?

I thought by just changing the source It would work but always get an error.

Have you a video on this subject?

I use power query but am missing the M language so I don’t get the point of the let _t ….

Thanks in advance

Roger

4

Think I’ll bookmark this post for later :grinning:

2 Likes
5

No problem @Roger,

I’ll add it here for others to review as well.
Included a second query, with separated steps. Hope that helps clarify it.

Please don’t forget to mark the response that solved your inquiry as solution, just click on the 3 dots. Thanks!
eDNA - Occurrence identifier.pbix (13.8 KB)

3 Likes
6

Melissa,

thanks for the solutions

kind regards

Roger