Forum Discussion
Keep first and last from grouped rows
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"
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
11 Replies
NewStep=Table.Combine(Table.Group(#"Sorted Rows","Unique ID",{"n",each if Table.RowCount(_)<3 then _ else Table.FirstN(_,1)&Table.LastN(_,1)})[n])
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
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 RemovedColumns1Thank 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.
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"this may give a duplicated row when there is only one row for a ID
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.
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
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
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."
