Join/Merge not working properly

[Anonymous]

Hi there!

I need to get the most recent amount of the product.

I have my table:

ProductID    ProductName      Date          Amount

10070	City Bike	12/15/2015	45,876
10045	Gravel Bike	12/15/2015	34,147
10060	Road Bike	12/15/2015	33,438
10010	Touring Bike	12/15/2015	32,555
10062	Children Bike	12/15/2015	31,686
10030	Mountain Bike	12/15/2015	22,752
10050	Cyclocross Bike	12/15/2015	19,733
10030	Mountain Bike	13/15/2015	22,752
10030	Mountain Bike	14/15/2015	22,752
10050	Cyclocross Bike	16/15/2015	19,733

The result I expect is the following:

ProductID     ProductName           Date        Amount

10070	City Bike	12/15/2015	45,876
10045	Gravel Bike	12/15/2015	34,147
10060	Road Bike	12/15/2015	33,438
10010	Touring Bike	12/15/2015	32,555
10062	Children Bike	12/15/2015	31,686
10030	Mountain Bike	14/15/2015	22,752
10050	Cyclocross Bike	16/15/2015	19,733

So, in PowerQuery I've done the following:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdA9C8IwEAbgvyKZD8zd5fqx6uDkIm6lQ9GiwdJAWwX/vUkhNYIoZEjC8745UlUKtc61ArW103O1sbfW75HWfpFG8QcjUOSZqmG2JlzthubRdovGRLMBNHnUWWg+uOb8tZkZDBfRYrBHdx9sf4mcPjiBiCzVFIa+2u48tP1SL6lHyIplcA71e3fvp8a+fZl4IsiFopf5U56nzp0GN44xoYskgQxclr9f4NQLsOGfnnTqtf+gfxNhlibIJ1DV9Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductID = _t, ProductName = _t, Date = _t, Amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"Date", type date}, {"Amount", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ProductID"}, {{"MaxDate", each List.Max([Date]), type nullable date}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ProductID"}, #"Table (2)", {"ProductID"}, "Table (2)", JoinKind.Inner),
#"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"ProductID", "ProductName", "Date", "Amount"}, {"Table (2).ProductID", "Table (2).ProductName", "Table (2).Date", "Table (2).Amount"})
in
#"Expanded Table (2)"

Basically:

I've done a group by PRODUCT ID on MAX DATE and then I've done an INNER JOIN with the original table(I've duplicated it before). All perfect. The problem comes when I expand the table, and it adds all the rows again, like no join was ever made. 

For the pbix with this example please click here.

I cannot find an explication for this, why does it add all those rows when I expand, why doesn't it just expand those new columns on the rows that I already have with the group by? This have been driving me crazy for days!

I would appreciate any help on this matter and finally understand this. It should be just a ismple inner join. 

Thanks in advance!

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

What date format are you using? What date is 16/15/2015?

The dates in the PBIX don't match the images above but no matter, the query still works.

After doing the Group By Product ID on Max Date, treat the resulting table as your left table.

Treat your (earlier) duplicated table with all your data as your right table.

Then do a Left Outer Join matching the Date and Product ID columns to give you only 1 row per Product in your final table.

Here's the PBIX file. 

NOTE: The image below is from your PBIX data, so doesn't look the same as the images you posted.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[wdx223_Daniel · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Custom1 = Table.FromRecords(Table.Group(Source,"ProductID",{"n",each Table.Sort(_,{"Date",1}){0}})[n])
in
    Custom1

[Anonymous]

Hey there. It looks to me like the right side of your join refers to #"Table (2)", which does not appear to exist in this query; perhaps it's somewhere outside of this query.

Assuming that when you say that you want to "inner join to the original table", that you mean the table as it was before it was grouped, you can write your join step like this:

Table.NestedJoin(#"Grouped Rows", {"ProductID"}, #"Changed Type", {"ProductID"}, "Table (2)", JoinKind.Inner)

#"Changed Type being the table as it was before the grouping. 

---Nate

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

What date format are you using? What date is 16/15/2015?

The dates in the PBIX don't match the images above but no matter, the query still works.

After doing the Group By Product ID on Max Date, treat the resulting table as your left table.

Treat your (earlier) duplicated table with all your data as your right table.

Then do a Left Outer Join matching the Date and Product ID columns to give you only 1 row per Product in your final table.

Here's the PBIX file. 

NOTE: The image below is from your PBIX data, so doesn't look the same as the images you posted.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[MattAllington]

I think this will help you https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/