Forum Discussion
DAX help: most recent event
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Customer Confirmation Number", "Delivery Confirmation Number"}, {{"Count", each Table.Max(_,{"Last Event Date","Last Event Time"})}}), #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}, {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"Last Event Date", type date}, {"Last Event Time", type time}}) in #"Changed Type"Hope this helps.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Customer Confirmation Number", "Delivery Confirmation Number"}, {{"Count", each Table.Max(_,{"Last Event Date","Last Event Time"})}}),
#"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}, {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"Last Event Date", type date}, {"Last Event Time", type time}})
in
#"Changed Type"Hope this helps.
Hi Ashish,
My dev team changed the file for me to use. I need to use the tracking number and the activity date to see the most up to date activity code. I need to see how the M code would be for these columns because I keep getting errors when trying to use the code from above and just insert it into the m code with changing the needed names:
| Warehouse Code | Client Name | Carrier Code | Ship Date | Tracking Number | Error Desc from Carrier | Process flag | Activity Code | Activity Date |
| 73011 | KS DFW | 90000011 | 2024/02/02 | 1ZR637F60107746480 | F | D | 20240205 |
Here is my M code that works right now but is not sorted:
let
Source = SharePoint.Files("https://pfsweb0.sharepoint.com/sites/CarrierTracking/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://pfsweb0.sharepoint.com/sites/CarrierTracking/Web Focus reports/")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Warehouse Code", Int64.Type}, {"Client Name", type text}, {"Carrier Code", Int64.Type}, {"Ship Date", type date}, {"Tracking Number", type text}, {"Error Desc from Carrier", type any}, {"Process flag", type text}, {"Activity Code", type text}, {"Activity Date", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"})
in
#"Removed Columns"
Cannot understand what you want. Share some data to work with, explain the question and show the expected result.