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.
Hello everyone,
I'm trying to filter a table on PowerQuery based on the date.
I have a list of several items, and these items might have been purchased on several dates, and I want to filter only the earliest line of each item (last purchase).
I'm all good doing that as calculated columns and stuff, but I was hoping to do it on PowerQuery to lighten the dataset. I'm struggling to find a solution to this.
Any help will be appreciated!
Thank you!
Solved! Go to Solution.
Hi @Anonymous ,
You could try below M code to see whether it work or not
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYiMDIwN9I31DpVgdiKARTNBQ3wguaIwQNDQFiyah6kcIGiEEjeGCSPqBKmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, po = _t, date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"po", Int64.Type}, {"date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"name", Order.Ascending}, {"date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"name"}, {{"all", each _, type table [name=text, po=number, date=date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "index",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"po", "date", "index"}, {"po", "date", "index"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([index] = 1)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"index"})
in
#"Removed Columns1"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Just FYI, each line of the same item has a different Purchase Order number, so I can't use Group By because I need both the latest late and the PO number of that latest date.
Hi @Anonymous ,
You could try below M code to see whether it work or not
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYiMDIwN9I31DpVgdiKARTNBQ3wguaIwQNDQFiyah6kcIGiEEjeGCSPqBKmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, po = _t, date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"po", Int64.Type}, {"date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"name", Order.Ascending}, {"date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"name"}, {{"all", each _, type table [name=text, po=number, date=date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "index",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"po", "date", "index"}, {"po", "date", "index"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([index] = 1)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"index"})
in
#"Removed Columns1"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous -
Please post/share sample data.
Proud to be a Super User!
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.