Power Query MAX Value from a table

Question

I trying to get the MAX date from a table onto a different date with power query. At the moment I'm stuck and all I get is a table based on a condition. Not sure if this is clear, so I'll explain with the code. This is my code at the moment:

let
    Source = Table.NestedJoin(Table.NestedJoin(SKU,{"SKU"},q_UltColh_NEW,{"SKU"},"qUltColh_NEW",JoinKind.LeftOuter),{"SKU"},r_STK,{"SKU"},"Rep_Stk", JoinKind.LeftOuter),
.
.
.
#"Expanded Origem" = ...
    #"Expanded Origem" = Table.ExpandTableColumn(#"Merged Queries", "Origem", {"Desc_ORI", "Parent_ORI"}, {"Origem.Desc_ORI", "Origem.Parent_ORI"}),
    #"Added Last_Rec" = Table.AddColumn(#"Expanded Origem", "Last_Rec", each 
                                                            let SKU = [SKU]
                                                            in Table.SelectRows(r_GOODSREC,each [SKU]=SKU)
)
in
    #"Added Last_Rec"

I have two tables:

SKU     Desc
46_24_  ABC
103_5_  DEF

doc_DATE    RowNo   SKU     Cod_ART     QTT
10/01/2017  1       46_24_  46.24       50
14/01/2017  1       46_24_  46.24       100
14/01/2017  1       103_5_  103.5       50
16/01/2017  1       103_5_  103.5       100

And I want to get:

SKU     Desc    Last_Entry  Qtt
46_24_  ABC     14/01/2017  50
103_5_  DEF     16/01/2017  100

my code is returning a table with various columns: SKU Desc Last_Entry
46_24_ ABC Table
103_5_ DEF Table

I believe once I get the max value I can just exapand the table, unless you tell me that is a bad ideia.

Thank you very much,

Marc Pincince Marc Pincince · Accepted Answer · 2017-09-05T00:09:13

I got this with the code below:

Notes:

1. My date format is month/day/year whereas yours was day/month/year.

2. Also, in your question, you show an expected QTT of 50 for SKU 46_24_; but your source table has 100 as the QTT for the latest date of SKU 46_24_, which is why my table has 100 instead of 50.

I used the same two starting tables as you. I called them Table1 and Table2. (Table1 is the one with just the SKU and Desc columns.)

Then I merged those two tables into a new table called Merge1, using a left-outer join.

I guess the key points are:

I used "Group By" (i.e., Table.Group) to group by each SKU and get its max date value in a column I called Last_Entry, and I included all row data in a column I called AllData. Here's the Group By pop-up window:

Then, after the Group By, I expanded the embedded table in AllData and added a new column to flag and filter out the rows where the doc_Date was not equal to Last_Entry.

let
Source = Table.NestedJoin(Table1,{"SKU"},Table2,{"SKU"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"doc_Date", "RowNo", "SKU", "Cod_ART", "QTT"}, {"doc_Date", "RowNo", "SKU.1", "Cod_ART", "QTT"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table2",{{"doc_Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, {{"Last_Entry", each List.Max([doc_Date]), type datetime}, {"AllData", each _, type table}}),
#"Expanded AllData1" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Desc", "doc_Date", "QTT"}, {"Desc", "doc_Date", "QTT"}),
#"Added Custom" = Table.AddColumn(#"Expanded AllData1", "Custom", each if[Last_Entry]=[doc_Date] then "Last_Entry" else "NotLast_Entry"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "Last_Entry")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "doc_Date"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"SKU", "Desc", "Last_Entry", "QTT"})
in
#"Reordered Columns"

Power Query MAX Value from a table

1 Answers