Forum Discussion
EF
4 years agoHelper II
Need Help: Re-structuring table and creating columns based on rows in Query Editor or Dax
Hi, I have a table that I need to restructure based on row values of the table. I found a long convoluted way to do it in Power Query but it is causing long refreshes and sometimes timing out (us...
- 4 years ago
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"
AlexisOlson
4 years agoSuper User
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"
EF
4 years agoHelper II
AlexisOlson it works!!
Thank you so much!
I didn't even mention but I had lost a lot of my work so you saved me hours of work AND gave me a much better solution!