Change Multiple entries to show first and last entry

[jbrines]

Hi Guys,

I am pulling door entry data from a SQL databaase that has two doors and an entry in or out from each door. Peole can go in and out of the door all day long, I only want to catch the first In record and the last out record.

Below is a screenshot of the report and the query.

Thanks

John

[speedramps]

Hi @jbrines 

You can accept more than one solution.

Please can you accept my solution.

I replied first and would appreciate scoring recognition points for helping.

Thank you.

[v-achippa · Accepted solution]

Hi @jbrines,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @speedramps for the prompt response.

Thank you for the response, please use this below code, it will add a Date column from the EventTime and add a new InOut column to classify. Group by FirstName, EventDate and get MIN for In and MAX for Out:

let

    Source = Sql.Database("ServerName", "DatabaseName"),

    sdk_EventsEx = Source{[Schema="sdk", Item="EventsEx"]}[Data],

    AddDate = Table.AddColumn(sdk_EventsEx, "EventDate", each Date.From([EventTime]), type date),

    AddInOut = Table.AddColumn(AddDate, "InOut", each if Text.Contains([PeripheralName], "(In)") then "In" else "Out"),

    Grouped = Table.Group(AddInOut, {"FirstName", "EventDate"},

        {

            {"EventTime (In)", each

                let

                    ins = Table.SelectRows(_, each [InOut] = "In"),

                    minIn = if Table.IsEmpty(ins) then null else List.Min(ins[EventTime])

                in minIn, type datetime},

            {"EventTime (Out)", each

                let

                    outs = Table.SelectRows(_, each [InOut] = "Out"),

                    maxOut = if Table.IsEmpty(outs) then null else List.Max(outs[EventTime])

                in maxOut, type datetime}

        }

    )

in

    Grouped

Now there will be one row per user per day Showing just the first "In" and last "Out" time.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[jbrines]

Hi @v-achippa ,

Is it possible to have the column "PeripheralName" showing at the end as wellas the other colnmns?

John

[jbrines]

Hi @v-achippa ,

Many thanks that worked a treat.

John.

[speedramps]

You have not menetioned if you want details for each user and each date.

Please try the GROUP BY methods I suggested, with MIN and MAX

Learn how here

https://learn.microsoft.com/en-us/power-query/group-by

It is easy

However, you may with to also add a date column so that you can Group By USER and DATE

to get the min and max date time

[jbrines]

Hi @speedramps ,

Yes I want details for each user and each date.

I will give Group by a try and get back to you.

Also is it possible to split it so the Periphapal Name (Out), Name and Event time are in different columns i.e.

Headings would be

First Name, Event Time (In) ,  Event Time out.

John

[jbrines]

Hi @speedramps 

User1 clocks in on 13/6/25 07:51

User1 clocks out on 13/6/25 12:01

User1 clocks in on 13/6/25 12:55

User1 clock out on 13/6/25 17:05

I only want to see 

User1 clocks in on 13/6/25 07:51

User1 clock out on 13/6/25 17:05

I hope that explains it better.

Thanks

John.

[speedramps]

In Power Query use the Group By  and Advance options.

You can then Group by Person and Min and Max date/time

or  Group by Person, date,  and Min and Max date/time

I want to help you but your description is too vague. Please write it againand provide excat details of how you want the Min and Max date grouped

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.