Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 (sim...
Need Help
  • slorin's avatar
    slorin
    3 years ago

    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

slorin's avatar
slorin
Super User
3 years ago

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