Forum Discussion
= Table.NestedJoin
It sounds like all your want to do is take the max week for each Order and then call it a closed service order on the max week.
Just Group By Order, Operation Max Week, Operation All Rows. Expand and add custom column Week = Max Week.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnKCs0BiRnAxI7iYMVwMwTIBs5yBLFM4y0wpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Week = _t]), #"Grouped Rows" = Table.Group(Source, {"Order"}, {{"Max Week", each List.Max([Week]), type nullable text}, {"Rows", each _, type table [Order=nullable text, Week=nullable text]}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Order"}), #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Columns", "Rows", {"Order", "Week"}, {"Order", "Week"}), #"Added Custom" = Table.AddColumn(#"Expanded Rows", "Closed", each [Week] = [Max Week]) in #"Added Custom"Copy and paste entire code into blank query to see a full example.
Solution Sage
This will create 1 empty table at the end. If you just want 8 tables then start the list at 1 instead of 0 and then tbl_list{c-1} and tbl_list{c} instead in the NestedJoin.
...
anti_join_list =
let
tbl_list = List.Buffer(prior[Transform File])
in
List.Accumulate(
{0 .. List.Count(tbl_list) - 1},
{},
(s,c) => s & { try Table.NestedJoin(tbl_list{c},{"ID"},tbl_list{c+1},{"ID"},"Join Name", JoinKind.RightAnti)
otherwise #table({},{}) }
)
in
anti_join_list
Hello, Can you please be more detailed on how to use the formula? I'm not quite sure where to copy it and if I need to "customize" it to make it work. I tried to make a function out of it to try to use in the invoke function column with no success. Also as
{"ID"}Tried to change it in the advanced editor
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns2",{"Index", "Year", "Week", "Source.Name", "Transform File"}),
#"Added Custom" =
anti_join_list =
let
tbl_list = List.Buffer(#"Removed Other Columns1",[Transform File])
in
List.Accumulate(
{0 .. List.Count(tbl_list) - 1},
{},
(s,c) => s & { try Table.NestedJoin(tbl_list{c},{"Index", "Year", "Week", "Source.Name", "Transform File"},tbl_list{c+1},{"Index", "Year", "Week", "Source.Name", "Transform File","Join Name"}, JoinKind.RightAnti)
otherwise #table({},{}) }
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Added Custom", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"
Thanks
- spinfuzer
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns2",{"Index", "Year", "Week", "Source.Name", "Transform File"}), anti_join_list = let tbl_list = List.Buffer(#"Removed Other Columns1"[Transform File]) in List.Accumulate( {0 .. List.Count(tbl_list) - 1}, {}, (s,c) => s & { try Table.NestedJoin(tbl_list{c},{"Index", "Year", "Week", "Source.Name", "Transform File"},tbl_list{c+1},{"Index", "Year", "Week", "Source.Name", "Transform File","Join Name"}, JoinKind.RightAnti) otherwise #table({},{}) } #"Expanded Table Column1" = Table.ExpandTableColumn(anti_join_list, "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))) in #"Expanded Table Column1"Are these really your join keys?
{"Index", "Year", "Week", "Source.Name", "Transform File"}
If so leave them alone, but I doubt that is what your a joining on right? It is probably just the Index or year/week. Those are supposed to the the columns that your are trying to match in each table. It surely isn't the Source.Name or Transform File column.
Join Keys are in the table contained in the "Trasform File" Column. They are "Source.Name" and "Order". The purpose of this formula is to list what is contained the the week before that is missing in the week after. So I want the same excact Table just, for example, with the order in Week 51 not listed in Week 52.
Still not Happy....
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns2",{"Index", "Year", "Week", "Source.Name", "Transform File"}),
#"Added Custom" =
anti_join_list =
let
tbl_list = List.Buffer(#"Removed Other Columns1"[Transform File])
in
List.Accumulate(
{0 .. List.Count(tbl_list) - 1},
{},
(s,c) => s & { try Table.NestedJoin(tbl_list{c},{"Source.Name", "Order"},tbl_list{c+1},{"Source.Name", "Order"}, JoinKind.RightAnti)
otherwise #table({},{}) }
#"Expanded Table Column1" = Table.ExpandTableColumn(anti_join_list, "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"- spinfuzer
remove the #"Added Custom"=
your step name is anti_join_list.