Keep first and last from grouped rows

[bluebird9]

Dear Power BI Community

Using an individual's Unique ID, I am trying to pair data using their first and last scores. Ideally I would like to keep all data in that row (e.g., raw score and T score).

I have grouped rows using an index column and named it 'Sub Order'. I am trying to select the first row and the last row. The first row is always 1 in 'Sub Order', however the last row will differ. I'm struggling to find a way to select first and last row, and delete everything else.

I have included a table of the example data, a table of the idea output, and the M code. 

I would be grateful for any advice.

Many thanks!

Example data

Unique ID	Unique Appointment ID	Raw score	T score	Sub Order
001	001-8-001	7	35	1
001	001-8-002	12	45	2
001	001-8-003	3	22	3
001	001-8-004	4	25	4
002	002-2-001	9	24	1
002	002-2-002	13	44	2
002	002-2-003	6	17	3
002	002-2-004	1	20	4
002	002-2-005	8	23	5

Ideal output

Unique ID	Unique Appointment ID	Raw score	T score	Sub Order
001	001-8-001	7	35	1
001	001-8-004	4	25	4
002	002-2-001	9	24	1
002	002-2-005	8	23	5

Advanced Editor code:

let
Source = Table.Combine({Full1, MDD1, GAD1, PD1, OCD1, SAD1, SOC1}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EVENT_TIME", type time}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Service ID", Order.Ascending}, {"Patient ID", Order.Ascending}, {"Date of referral", Order.Ascending}, {"EVENT_DATE", Order.Ascending}, {"EVENT_TIME", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Unique ID"}, {{"Count", each _, type table [Service name=nullable text, Service ID=nullable number, Patient ID=nullable number, Date of referral=nullable date, Unique ID=nullable text, EVENT_DATE=nullable date, EVENT_TIME=nullable time, Unique Appointment ID=nullable text, GENDER=nullable text, Gender code=nullable number, Age=nullable number, Grade=nullable number, SAD raw=nullable number, GAD raw=nullable number, PD raw=nullable number, SOC raw=nullable number, OCD raw=nullable number, MDD raw=nullable number, Total Anxiety raw=nullable number, Total RCADS raw=nullable number, SAD T=nullable number, GAD T=nullable number, PD T=nullable number, SOC T=nullable number, OCD T=nullable number, MDD T=nullable number, Total Anxiety T=nullable number, Total RCADS T=nullable number, Index=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Sub Order", 1, 1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Service name", "Service ID", "Patient ID", "Date of referral", "EVENT_DATE", "EVENT_TIME", "Unique Appointment ID", "GENDER", "Gender code", "Age", "Grade", "SAD raw", "GAD raw", "PD raw", "SOC raw", "OCD raw", "MDD raw", "Total Anxiety raw", "Total RCADS raw", "SAD T", "GAD T", "PD T", "SOC T", "OCD T", "MDD T", "Total Anxiety T", "Total RCADS T", "Index", "Sub Order"}, {"Service name", "Service ID", "Patient ID", "Date of referral", "EVENT_DATE", "EVENT_TIME", "Unique Appointment ID", "GENDER", "Gender code", "Age", "Grade", "SAD raw", "GAD raw", "PD raw", "SOC raw", "OCD raw", "MDD raw", "Total Anxiety raw", "Total RCADS raw", "SAD T", "GAD T", "PD T", "SOC T", "OCD T", "MDD T", "Total Anxiety T", "Total RCADS T", "Index", "Sub Order"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"})
in
#"Removed Columns"

[bluebird9]

Thank you HotChilli for taking the time to answer my question. I managed to to get as far as Table.SelectRows, but when I run the code, the data disappears, leaving only the headings. I will do my best to figure it out, but if you have any suggestions as to why this might be happening, or ways to problem solve, I would be interested in hearing them.

This is the final line of code: = Table.SelectRows(#"Expanded All rows", each ([#"Sub Area No."] = "Min" and "Max"))

Many thanks

[HotChilli]

Respect to you for getting in there and trying it.

The last line will be something like:

Table.SelectRows(#"Expanded All rows", each [#"Sub Area No."] = [Min] or [#"Sub Area No."] = [Max])

Check that the datatypes match too.

All the best

[bluebird9]

Thanks, I do enjoy problem solving.

Thank you for the last line. It's good to know where I was going wrong, and how to correctly write the code. Checked the datatypes, and with a quick name change it worked perfectly. Thank you again, really appreciate it.

[Anonymous]

Hello I have this problem when I connect to Oracle Data Base,  My laptop have a windows 11 Pro.

"El proveedor que se está usando está desusado: 'System.Data.OracleClient requiere la version 8.1.7 o posterior del software cliente de Oracle.'. Visite https://go.microsoft.com/fwlink/p/?LinkID=272376 para instalar el proveedor oficial." 

[wdx223_Daniel]

NewStep=Table.Combine(Table.Group(#"Sorted Rows","Unique ID",{"n",each if Table.RowCount(_)<3 then _ else Table.FirstN(_,1)&Table.LastN(_,1)})[n])

[Jakinta]

Or like this...

let
    Source = YourPreviousStep,
    #"Grouped Rows" = Table.Group(Source, {"Unique ID"}, {{"All", each Table.FirstN(Table.Sort(_,{{"Sub Order", Order.Ascending}}) ,1) & Table.LastN(Table.Sort(_,{{"Sub Order", Order.Ascending}}) ,1), type table [Unique ID=number, Unique Appointment ID=text, Raw score=number, T score=number, Sub Order=number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Unique ID"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Unique ID", "Unique Appointment ID", "Raw score", "T score", "Sub Order"}, {"Unique ID", "Unique Appointment ID", "Raw score", "T score", "Sub Order"})
in
    #"Expanded All"

[wdx223_Daniel]

this may give a duplicated row when there is only one row for a ID 

[bluebird9]

Thank you Jakinta and wdx223_Daniel for your combined efforts on this problem.

wdx223_Daniel - when I added your next step to the M code provided by Jakinta, it worked perfectly! Thank you both again.

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.

let
    Source = Table.Combine({Full1, MDD1, GAD1, PD1, OCD1, SAD1, SOC1}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EVENT_TIME", type time}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Service ID", Order.Ascending}, {"Patient ID", Order.Ascending}, {"Date of referral", Order.Ascending}, {"EVENT_DATE", Order.Ascending}, {"EVENT_TIME", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Unique ID"}, {{"Count", each _, type table [Service name=nullable text, Service ID=nullable number, Patient ID=nullable number, Date of referral=nullable date, Unique ID=nullable text, EVENT_DATE=nullable date, EVENT_TIME=nullable time, Unique Appointment ID=nullable text, GENDER=nullable text, Gender code=nullable number, Age=nullable number, Grade=nullable number, SAD raw=nullable number, GAD raw=nullable number, PD raw=nullable number, SOC raw=nullable number, OCD raw=nullable number, MDD raw=nullable number, Total Anxiety raw=nullable number, Total RCADS raw=nullable number, SAD T=nullable number, GAD T=nullable number, PD T=nullable number, SOC T=nullable number, OCD T=nullable number, MDD T=nullable number, Total Anxiety T=nullable number, Total RCADS T=nullable number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Sub Order", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Service name", "Service ID", "Patient ID", "Date of referral", "EVENT_DATE", "EVENT_TIME", "Unique Appointment ID", "GENDER", "Gender code", "Age", "Grade", "SAD raw", "GAD raw", "PD raw", "SOC raw", "OCD raw", "MDD raw", "Total Anxiety raw", "Total RCADS raw", "SAD T", "GAD T", "PD T", "SOC T", "OCD T", "MDD T", "Total Anxiety T", "Total RCADS T", "Index", "Sub Order"}, {"Service name", "Service ID", "Patient ID", "Date of referral", "EVENT_DATE", "EVENT_TIME", "Unique Appointment ID", "GENDER", "Gender code", "Age", "Grade", "SAD raw", "GAD raw", "PD raw", "SOC raw", "OCD raw", "MDD raw", "Total Anxiety raw", "Total RCADS raw", "SAD T", "GAD T", "PD T", "SOC T", "OCD T", "MDD T", "Total Anxiety T", "Total RCADS T", "Index", "Sub Order"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    GroupedRows = Table.Group(#"Changed Type", {"Unique ID"}, {{"Min", each List.Min([Sub Order]), type nullable number}, {"Max", each List.Max([Sub Order]), type nullable number}}),
    AddedCustom = Table.AddColumn(GroupedRows, "Custom", each {[Min]}&{[Max]}),
    ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom"),
    RemovedColumns = Table.RemoveColumns(ExpandedCustom,{"Min", "Max"}),
    MergedQueries = Table.NestedJoin(#"Changed Type", {"Unique ID", "Sub Order"}, RemovedColumns, {"Unique ID", "Custom"}, "Removed Columns", JoinKind.LeftOuter),
    ExpandedRemovedColumns = Table.ExpandTableColumn(MergedQueries, "Removed Columns", {"Unique ID"}, {"Unique ID.1"}),
    FilteredRows = Table.SelectRows(ExpandedRemovedColumns, each ([Unique ID.1] <> null)),
    RemovedColumns1 = Table.RemoveColumns(FilteredRows,{"Unique ID.1"})
in
    RemovedColumns1

[bluebird9]

Thank you Vijay_A_Verma for taking the time to write out the code for me. After the smallest tweak, the code worked perfectly. Thank you very much for your help.

[HotChilli]

One way to do this is to Group on the ID, returning aggregations on Max(sub order), Min(sub order) and 'all rows'.

Expand the 'all rows' in the next stage and use Table.SelectRows to keep the rows where SubOrder = the min or max.

Have a go and I'll help if you get stuck