Lookup a value in same table

[Anonymous]

Hi,

I am trying to do a lookup and calculation in Power Query, but cannot figure it out.

I need to find the previous date in a table, based on filters and subtract a day.

Start table (simplyfied):

List	Product	Startdate	Price
L0001	Product A	1-1-2022	1
L0001	Product A	13-2-2022	1,01
L0001	Product A	21-4-2022	1,05
L0001	Product A	1-11-2022	1,03
L0002	Product A	1-2-2022	1,05
L0002	Product A	1-2-2023	1,25
L0002	Product B	1-2-2023	5,2
L0002	Product A	1-4-2023	1,31
L0003	Product C	1-1-2022	0,35
L0003	Product C	31-10-2022	0,36
L0003	Product C	1-4-2023	0,41
L0003	Product D	31-10-2022	4,01
L0003	Product D	1-4-2023	4,18
L0001	Product A	1-5-2023	1,15
L0001	Product D	1-5-2023	4,01

End table:

List	Product	Startdate	Price	Enddate
L0001	Product A	1-1-2022	1	12-2-2022
L0001	Product A	13-2-2022	1,01	20-4-2022
L0001	Product A	21-4-2022	1,05	31-10-2022
L0001	Product A	1-11-2022	1,03	30-4-2023
L0002	Product A	1-2-2022	1,05	31-1-2023
L0002	Product A	1-2-2023	1,25	31-3-2023
L0002	Product B	1-2-2023	5,2
L0002	Product A	1-4-2023	1,31
L0003	Product C	1-1-2022	0,35	30-10-2022
L0003	Product C	31-10-2022	0,36	31-3-2023
L0003	Product C	1-4-2023	0,41
L0003	Product D	31-10-2022	4,01	31-3-2023
L0003	Product D	1-4-2023	4,18
L0001	Product A	1-5-2023	1,15
L0001	Product D	1-5-2023	4,01

Formula used in the column Enddate (row 2) in Excel would be:

=IF(
MIN( IF( C:C>C2; IF( B:B'=B2; IF( A:A'=A2; C:C))))=0;
"";
MIN( IF( C:C>C2; IF( B:B'=B2; IF( A:A'=A2; C:C))))-1)

Or in words: Find the smallest [startdate] that is larger than the current [startdate] and has the same [list] and [product] and subtract one. If you cannot find one, leave blank.

I am trying to figure out how to do this in Power query, not in dax.

Any tips?

[Anonymous]

Thanks!
I am going to do some research on the list.transform and list.skip queries to understand it better, but it works.

Thanks a lot

[slorin · Accepted solution]

Hi

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZE9CgIxEIWvIqkHdv4ituqWFvbLVtoLovfxLJ7MQSGJwyQkRUK+eW9eZlnSCREpQTrfb9fn5bHZ2xlpss3IbBd6v+KVVojLSSbkUg723iPZjLQhc5e0fogaUgrJQfMcanZJ+ZIckwdPZuChpFZJqdGlIY/+jxEkd0mx6NiQ26FmcUfQ2H32mtqOyJN/mgq0G40Ic81O8TBnT/7c1w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [List = _t, Product = _t, Startdate = _t, Price = _t]),
Type = Table.TransformColumnTypes(Source,{{"Startdate", type date}, {"Price", type number}}),
Group = Table.Group(
Type,
{"List", "Product"},
{{"Data", each Table.FromColumns(
{
[Startdate],
[Price],
List.Transform(List.Skip([Startdate]), each Date.AddDays(_,-1))
},
{"Startdate", "Price", "Enddate"}),
type table [Startdate=nullable date, Price=nullable number, Enddate=nullable date]}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"Startdate", "Price", "Enddate"}, {"Startdate", "Price", "Enddate"})
in
Expand

Stéphane