Forum Discussion
Need Help: Re-structuring table and creating columns based on rows in Query Editor or Dax
It's not super simple, but List.Generate might be faster than what you've tried.
Example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7JTM0rcVTSUTLUN9Q3MjAyADJ9lWJ1kKWMEFKeaFLGCCkFJCkn3AYiSRli0WWEkMKhywjNGc6odvkrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [people_id = _t, el_actual_date = _t, program_status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"people_id", type text}, {"el_actual_date", type date}, {"program_status", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"people_id"}, {{"SubTable", (A) => Table.Skip(Table.FromRecords( List.Generate( () => [t = [people_id = null, el_actual_date = null, program_status = null], i = 0, j = 0, d = #date(1900,1,1)], each [i] <= Table.RowCount(A), each [ i = [i] + 1, d = A{[i]}[el_actual_date], j = (if d < Date.AddDays([d], 90) then [j] else [j] + 1), t = A{[i]} ], each [t] & [episode_of_care_num = [j]] ))) }}), #"Expanded SubTable" = Table.ExpandTableColumn(#"Grouped Rows", "SubTable", {"el_actual_date", "program_status", "episode_of_care_num"}, {"el_actual_date", "program_status", "episode_of_care_num"}), #"Remove Non-MO" = Table.TransformColumns(#"Expanded SubTable",{{"program_status", each if _ = "M" or _ = "O" then _ else "", type text}}), #"Grouped Rows1" = Table.Group(#"Remove Non-MO", {"people_id", "episode_of_care_num"}, {{"eoc_start_date", each List.Min([el_actual_date]), type date}, {"eoc_end_date", each List.Max([el_actual_date]), type date}, {"M_or_O_status", each List.Max([program_status]), type text}}) in #"Grouped Rows1"
It's not super simple, but List.Generate might be faster than what you've tried.
Example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7JTM0rcVTSUTLUN9Q3MjAyADJ9lWJ1kKWMEFKeaFLGCCkFJCkn3AYiSRli0WWEkMKhywjNGc6odvkrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [people_id = _t, el_actual_date = _t, program_status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"people_id", type text}, {"el_actual_date", type date}, {"program_status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"people_id"}, {{"SubTable",
(A) => Table.Skip(Table.FromRecords(
List.Generate(
() => [t = [people_id = null, el_actual_date = null, program_status = null],
i = 0, j = 0, d = #date(1900,1,1)],
each [i] <= Table.RowCount(A),
each [
i = [i] + 1,
d = A{[i]}[el_actual_date],
j = (if d < Date.AddDays([d], 90) then [j] else [j] + 1),
t = A{[i]}
],
each [t] & [episode_of_care_num = [j]]
)))
}}),
#"Expanded SubTable" = Table.ExpandTableColumn(#"Grouped Rows", "SubTable", {"el_actual_date", "program_status", "episode_of_care_num"}, {"el_actual_date", "program_status", "episode_of_care_num"}),
#"Remove Non-MO" = Table.TransformColumns(#"Expanded SubTable",{{"program_status", each if _ = "M" or _ = "O" then _ else "", type text}}),
#"Grouped Rows1" = Table.Group(#"Remove Non-MO", {"people_id", "episode_of_care_num"}, {{"eoc_start_date", each List.Min([el_actual_date]), type date}, {"eoc_end_date", each List.Max([el_actual_date]), type date}, {"M_or_O_status", each List.Max([program_status]), type text}})
in
#"Grouped Rows1"
Hi AlexisOlson ,
The parameters of this dataset have changed and I am stuck again!
I tried adjusting your code but I do not have a good grasp of List.Generate; I would appreciate your help.
I am still trying to list people by episode of care number, with a start date and end date.
I no longer need program_status.
I have 3 new columns:
[keepforLOS] which has each row categorized as either "session", "intake" or "discharge".
[source] which is either "Accumed" or "Netsmart"
[providerID]
if, for a particular client, the row is [source] = "Accumed" and the row directly before was [keepforLOS] "Intake" that is the start date of a new Episode of Care (EOC).
any sessions following that date for that client are part of the same EOC until there is a new row [keepforLOS]="intake".
if the [source] = "Netsmart" and there are no rows for that client previously, then that is a new EOC. the EOC continues until there is a "discharge", which ends the EOC and the next row would start a new EOC.
1 exception (to complicate matters even more!!) If the session row following Discharge is the same provider as the last Session before Discharge, AND it has been less than 90 days between the 2 sessions, then there is no new episode of care.
ex:
client | date | keepforLOS | source | Provider
A | 1/1/2015| session | Accumed | A
A | 2/1/2015 | intake | Accumed | B
A | 3/1/2015 | session | Accumed | B
A | 1/1/2020 | session | Netsmart | B
A | 2/1/2020 | discharge | Netsmart | B
A | 3/1/2020 | session | Netsmart | C
B | 1/1/2020| session | Netsmart | A
B| 2/1/2020 | discharge | Netsmart | A
B | 3/1/2020 | session | Netsmart | B
B | 4/1/2020 | sesion | Netsmart | C
C | 1/1/2020 | session | Netsmart | A
C | 2/1/2020 | discharge | Netsmart | A
C | 2/15/2020 | session | Netsmart | A
expected results:
Client | EOC# | start_date | end_date
A | 1 | 2/1/2015 | 1/1/2020
A | 2 | 3/1/2020 | 3/1/2020
B | 1 | 1/1/2020 | 1/1/2020
B | 2 | 3/1/2020 | 4/1/2020
C | 1 | 1/1/2020 | 2/15/2020
Can your code be adjusted for these new parameters?
TIA!
EF