Forum Discussion
Filter LastDate PowerQUery
Best regards...
I hope you can help me with this request. I have 2 tables, both with a USER IDENTIFIER.
Table 1 : 1 user can have many orders, each order has its date. I need the last date of each order, obvious with the rest of the columns that detail that order.
Table 2 : 1 user can have many sales, each sale has its date. I need the last sale of that user, obvious with the rest of the columns that detail that sale.
then get a third table in merge with those 2 above.
Objective to have a table :
user --- LastOrderDate --- Details order -- LastSellDate -- Detail sell
Through Dax, columns and calculated tables I was able to achieve the first 2 tables with the last date of order and sale, but although I relate them I can not join them in 1 single pagination.
Which leads me to want to do this on PowerQuery, but I don't master it.
Additional fact: with similar things but I have many tables that I need to relate "many to many" and PowerBI is not very docile for this transformation, so solving this case would help me with the others.
Thank you in advance.
Hi avillarroelk250 , it looks like you want to find and keep the details for the last Order and Sale for each customer. In Power Query, you could achieve this using the following steps:
- Sort the individual tables by Customer and Date.
- Add Index column column - this will find the max id if there are more sales or orders on last date.
- Add a Boolean (true/false) column indicating if the Row is the max id for the custom. This is the complex part.
- Filter the tables for True.
- Merge the tables based on Customer ID.
Try the following example in Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjIyAnMSc7JU0pVidayQlTKqU4LSsHLOmMKVmckpZlDZaEGWoElyxOyYHpBBtrjCyZlpKTnYUw1QRVLgsmiWlqYkpaDopTjRH6crKBvogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Sale Date" = _t, #"Sale Detail" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sale Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer", Order.Ascending}, {"Sale Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "ID", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "IsMax",
each let
x = [Customer]
, filter = Table.SelectRows( #"Added Index" , each [Customer] = x )
, result = List.Max( filter[ID] )
in
result = [ID]
, type logical
),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([IsMax] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ID", "IsMax"})
in
#"Removed Columns"
2 Replies
Hi avillarroelk250 , it looks like you want to find and keep the details for the last Order and Sale for each customer. In Power Query, you could achieve this using the following steps:
- Sort the individual tables by Customer and Date.
- Add Index column column - this will find the max id if there are more sales or orders on last date.
- Add a Boolean (true/false) column indicating if the Row is the max id for the custom. This is the complex part.
- Filter the tables for True.
- Merge the tables based on Customer ID.
Try the following example in Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjIyAnMSc7JU0pVidayQlTKqU4LSsHLOmMKVmckpZlDZaEGWoElyxOyYHpBBtrjCyZlpKTnYUw1QRVLgsmiWlqYkpaDopTjRH6crKBvogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Sale Date" = _t, #"Sale Detail" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sale Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Customer", Order.Ascending}, {"Sale Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "ID", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "IsMax",
each let
x = [Customer]
, filter = Table.SelectRows( #"Added Index" , each [Customer] = x )
, result = List.Max( filter[ID] )
in
result = [ID]
, type logical
),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([IsMax] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ID", "IsMax"})
in
#"Removed Columns"Gracias. Voy a probar esta solucion. al tener un feedback te dejo saber.
