Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
The SQL table I'm pulling has millions of rows and I only need the last 100 records for each unique program in a column. See generic example below for pulling last 3 records for each unique program in a column:
Date | Program |
1/1/2020 | D |
1/2/2020 | A |
1/3/2020 | D |
1/4/2020 | A |
1/5/2020 | B |
1/6/2020 | B |
1/7/2020 | A |
1/8/2020 | D |
1/9/2020 | B |
1/10/2020 | D |
1/11/2020 | B |
1/12/2020 | D |
1/13/2020 | C |
1/14/2020 | B |
1/15/2020 | C |
1/16/2020 | C |
1/17/2020 | C |
1/18/2020 | C |
1/19/2020 | A |
1/20/2020 | A |
I want to only return this:
Date | Program |
1/20/2020 | A |
1/19/2020 | A |
1/7/2020 | A |
1/14/2020 | B |
1/11/2020 | B |
1/9/2020 | B |
1/18/2020 | C |
1/17/2020 | C |
1/16/2020 | C |
1/12/2020 | D |
1/10/2020 | D |
1/8/2020 | D |
Solved! Go to Solution.
Here is one way to do it by grouping your data, sorting and keep the last 3 dates for each. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below that transforms your example data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc/JCcAwDETRXnQ2RFK8HrN0Ydx/GyFghzBzffwRdu9im22urhLklhFe8AXHhB2LiEVacE7ICAUnFY82nJhiYkaNU/O99loSaZWoySSFpJI0/JXrT8YD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Program = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Program"}, {{"AllRows", each _, type table [Date=nullable text, Program=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Last3Dates", each Table.FirstN(Table.Sort([AllRows], {{"Date", Order.Descending}}),3)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
#"Expanded Last3Dates" = Table.ExpandTableColumn(#"Removed Columns", "Last3Dates", {"Date"}, {"Date"})
in
#"Expanded Last3Dates"
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is one way to do it by grouping your data, sorting and keep the last 3 dates for each. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below that transforms your example data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc/JCcAwDETRXnQ2RFK8HrN0Ydx/GyFghzBzffwRdu9im22urhLklhFe8AXHhB2LiEVacE7ICAUnFY82nJhiYkaNU/O99loSaZWoySSFpJI0/JXrT8YD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Program = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Program"}, {{"AllRows", each _, type table [Date=nullable text, Program=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Last3Dates", each Table.FirstN(Table.Sort([AllRows], {{"Date", Order.Descending}}),3)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
#"Expanded Last3Dates" = Table.ExpandTableColumn(#"Removed Columns", "Last3Dates", {"Date"}, {"Date"})
in
#"Expanded Last3Dates"
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.