Active Member
May 27, 2020
Dear All
I struggle by filtering a nested table.
What I do not understand is, why I can filter my nested table using value from outer table by adding a new column,
but I cannot do same filtering by using command Table.TransformColumns on the original column with the nested tables.
The error tells me that the column "Date" from outer table was not found. What mistake do I do ?
This is my script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc+7DcAwDMTQXVQbhk75l7k1DO+/Rpo0CVtWfGPEHS1SXdUrSzHbm4ppYVqZNqad6WA6mS4kJRPvxXt97022yTbZJttkm2yTbbJNtsk22SbbP/Z8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code = _t, date = _t]),
Chg_Type = Table.TransformColumnTypes(Source,{{"code", type text}, {"date", type date}}),
Group = Table.Group(Chg_Type, {"code"}, {{"Data", each _, type table [code=nullable text, date=nullable date]}}),
Sort_Group = Table.TransformColumns(Group, {"Data", each Table.Sort(_,{{"code", Order.Ascending},{"date", Order.Ascending}})}),
Index_Group = Table.TransformColumns(Sort_Group, {"Data", each Table.AddIndexColumn(_,"Index",1,1), type table}),
Datum = Table.AddColumn(Index_Group, "Date", each Table.Column([Data], "date")),
Exp_Datum = Table.ExpandListColumn(Datum, "Date"),
Chg_Type1 = Table.TransformColumnTypes(Exp_Datum,{{"Date", type date}}),
// this works by adding a new column
Filter_date = Table.AddColumn(Chg_Type1, "Data1", each Table.SelectRows([Data], (r) => r[date] <= [Date] and r[date] >= Date.AddDays([Date], -6))),
// this does not work
X = Table.TransformColumns(Filter_date, {"Data", each Table.SelectRows(_, (inner) => inner[date] <= [Date] and inner[date] >= Date.AddDays([Date], -6))})
in
X
Thanks for your help.
Best regards
Joerg
1 Guest(s)