Forum Discussion

srhoag's avatar
srhoag
Regular Visitor
9 years ago

Find most current date an item was cycle counted...

Sorry for this exceedingly basic question, but I am trying to report cycle count data for our auditors.   They only want to see the most recent cycle count on each item.   So how do I tell PowerBI to...
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

This can easily be done in Power Query (Get and transform data):
group by Item and select all rows, This will give you a table with items and a column with a table with all data for that item,

Add a column selecting the maximum date from each of those item tables.

This will give you a record with the data of the most recent date.

Expand that column and choose fields Posting Date and Physical Count.

Below the code created in Excel with input table "CycleCounts".

 

let
    Source = Excel.CurrentWorkbook(){[Name="CycleCounts"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Number", Int64.Type}, {"Posting Date", type datetime}, {"Physical Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item Number"}, {{"OtherData", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([OtherData], "Posting Date")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Posting Date", "Physical Count"}, {"Posting Date", "Physical Count"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Posting Date", type date}, {"Physical Count", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"OtherData"})
in
    #"Removed Columns"