Forum Discussion
Lookup a value in same table
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?
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
ExpandStéphane
2 Replies
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
ExpandStéphane