Forum Discussion
Get all rows from a table based on multiple values on my current table
Hi, try this. Should work faster.
let // table 01 data tbl1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY2VtJRMjDSNzLQNwIxQzJzU+MNlWJ14HLGeORM8OszBcrFlKDKmpiYAAUM9Q2BWsE6S0zB4qampkCOEUI8zUQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [material_id = _t, dates_01 = _t, #"Capacity needed_OLD" = _t]), type_01 = Table.TransformColumnTypes(tbl1,{{"dates_01", type date}}), // group table 01 by material_id and sort each table by date g1 = Table.Group(type_01, {"material_id"}, {{"tbl1", each Table.Sort(_, {"dates_01", Order.Descending})}}), // table 02 data tbl2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY2VtJRMjDWNzTVNwIxQzJzU+ONlGJ14HImqHLGYDkjIyOggKG+ob6RAViuBCRnbm4OljUxMcEia2lpCZY1NTWFyEKNBUuCBGNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [material_id = _t, dates_02 = _t, #"Improved Time" = _t]), type_02 = Table.TransformColumnTypes(tbl2,{{"dates_02", type date}}), // group table by material_id and make a record (dict) with material_id as field names g2 = Table.Group(type_02, {"material_id"}, {{"Value", each List.Zip({[dates_02], [Improved Time]})}}), dict = Record.FromTable(Table.RenameColumns(g2,{{"material_id", "Name"}})), // this is join replacement. Should work faster! tbl = Table.AddColumn(g1, "tbl2", each Record.FieldOrDefault(dict, [material_id], null)), // this function iterates table "old" (resides in tbl1 column) and grabs date from tbl2 fx_new_capacity = (old as table, new as list) => let old_count = Table.RowCount(old), dates = List.Buffer( List.Sort( old[dates_01], Order.Descending ) ), updates = List.Buffer( List.Sort( new, {each _{0}, Order.Descending} ) ), new_column = List.Generate( () => [ i = 0, lst = updates, new_col = {}], (x) => x[i] <= old_count and not List.IsEmpty( x[lst] ), (x) => let a = List.Skip ( x[lst], each _{0} >= dates{x[i]} ), b = List.First( a ){1} in [ i = x[i] + 1, lst = a, new_col = x[new_col] & {b} ] ), out = Table.FromColumns( Table.ToColumns(old) & {List.Last(new_column)[new_col]}, Table.ColumnNames(old) & {"Capacity_neededNEW"} ) in out, // here we iterate table tbl and call our function, then combine our results into final table new_cap = Table.Combine(Table.TransformRows( tbl, each if [tbl2] = null then [tbl1] else fx_new_capacity( [tbl1], [tbl2] ))) in new_cap
Hi AlienSx , thank you for the reply.
In table2 there are 2 arguments that I need for calculation.
In table1 are all my production plans for every material and the time capacity they need,. There is a possibility for planned improvement where the needed capacity changes for a material throughout a year (maybe more than one time), table 2 contains this information.
What I want is, that if the time was changed before a planned production date, than the capacity needed should change according to the new time from table2 (after getting a values I check if the values exist, if not I use the old time, if yes I use the new ones).
Sorry I can not get more into detail, since it is a company task.
H_George , lets simplify this. Suppose you have 2 tables with same material_id: from tbl01 and tbl02 respectively.
tbl01
material_id dates_01 333 02/20/23 333 03/20/23 333 04/20/23 tbl02 selection with the same material_id
material_id dates_02 333 01/15/23 333 03/01/23 what would you want to select from tbl02?
As far as I can understand, you are iterating tbl_01 row by row, then select records from tbl02 with date less than current value in tbl01[dates_01], then select a single record using List.Max(tbl01[dates_02]). Correct? Could you please show the final selection from the tbl02 using table above? You may change the data if you find them irrelevant.
Hi,
table1material_ID dates_01 Capacity needed_OLD Capacity needed_NEW 333 02/20/23 Time_1 333 03/20/23 Time_1 Time_2 333 03/25/23 Time_1 Time_2 333 04/20/23 Time_1 Time_3 table2
material_ID dates_02 Improved Time 333 03/15/23 Time_2 333 04/15/23 Time_3
After the join I use a formula like this:
let val = [dates_01] in List.Count(List.Select(Table.Column([table2], "dates_02"), each _ <=val))
I store this value in a column as index.
Then I get the time with this:
Table.Column([table], "Improved Time"){[Index]-1}
I do this for every column I need(the time value is based on multiple columns, but I wouldn't get into it much more than this)
There has to be a better solution.
Also the best would be to replace the original times(this is historic data), right now I use an IF statement to check if the new time is empty or not, and that how I calculate with it.
Thank you for your time.Hi, try this. Should work faster.
let // table 01 data tbl1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY2VtJRMjDSNzLQNwIxQzJzU+MNlWJ14HLGeORM8OszBcrFlKDKmpiYAAUM9Q2BWsE6S0zB4qampkCOEUI8zUQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [material_id = _t, dates_01 = _t, #"Capacity needed_OLD" = _t]), type_01 = Table.TransformColumnTypes(tbl1,{{"dates_01", type date}}), // group table 01 by material_id and sort each table by date g1 = Table.Group(type_01, {"material_id"}, {{"tbl1", each Table.Sort(_, {"dates_01", Order.Descending})}}), // table 02 data tbl2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY2VtJRMjDWNzTVNwIxQzJzU+ONlGJ14HImqHLGYDkjIyOggKG+ob6RAViuBCRnbm4OljUxMcEia2lpCZY1NTWFyEKNBUuCBGNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [material_id = _t, dates_02 = _t, #"Improved Time" = _t]), type_02 = Table.TransformColumnTypes(tbl2,{{"dates_02", type date}}), // group table by material_id and make a record (dict) with material_id as field names g2 = Table.Group(type_02, {"material_id"}, {{"Value", each List.Zip({[dates_02], [Improved Time]})}}), dict = Record.FromTable(Table.RenameColumns(g2,{{"material_id", "Name"}})), // this is join replacement. Should work faster! tbl = Table.AddColumn(g1, "tbl2", each Record.FieldOrDefault(dict, [material_id], null)), // this function iterates table "old" (resides in tbl1 column) and grabs date from tbl2 fx_new_capacity = (old as table, new as list) => let old_count = Table.RowCount(old), dates = List.Buffer( List.Sort( old[dates_01], Order.Descending ) ), updates = List.Buffer( List.Sort( new, {each _{0}, Order.Descending} ) ), new_column = List.Generate( () => [ i = 0, lst = updates, new_col = {}], (x) => x[i] <= old_count and not List.IsEmpty( x[lst] ), (x) => let a = List.Skip ( x[lst], each _{0} >= dates{x[i]} ), b = List.First( a ){1} in [ i = x[i] + 1, lst = a, new_col = x[new_col] & {b} ] ), out = Table.FromColumns( Table.ToColumns(old) & {List.Last(new_column)[new_col]}, Table.ColumnNames(old) & {"Capacity_neededNEW"} ) in out, // here we iterate table tbl and call our function, then combine our results into final table new_cap = Table.Combine(Table.TransformRows( tbl, each if [tbl2] = null then [tbl1] else fx_new_capacity( [tbl1], [tbl2] ))) in new_cap