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.
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
Solution Sage#"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.
Thanks for you patience. There is an other let on top of the whole formula
let
Source = Folder.Files("C:\TO - Installazioni - TO - Installazioni\SAP Weekly Data\FY2023-2024"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns1", "Index", 1, 1, Int64.Type),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"Source.Name", Order.Descending}}),
#"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Index", "Content", "Source.Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Transform File"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Source.Name", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Source.Name.1", "Source.Name.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Source.Name.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Source.Name.1", "Source.Name"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Source.Name", "Source.Name - Copy"),
#"Reordered Columns1" = Table.ReorderColumns(#"Duplicated Column",{"Index", "Content", "Source.Name", "Source.Name - Copy", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Transform File"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Reordered Columns1", "Source.Name", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Source.Name.1", "Source.Name.2", "Source.Name.3"}),
#"Renamed Columns2" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Source.Name.1", "Year"}, {"Source.Name.3", "Week"}, {"Source.Name - Copy", "Source.Name"}}),
#"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},{"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
All of the other lets and stuff doesn't really matter, you just need the syntax right on the things you add if everything else was already correct.
let Source = Folder.Files("C:\TO - Installazioni - TO - Installazioni\SAP Weekly Data\FY2023-2024"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns1", "Index", 1, 1, Int64.Type), #"Sorted Rows" = Table.Sort(#"Added Index",{{"Source.Name", Order.Descending}}), #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Index", "Content", "Source.Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Transform File"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Source.Name", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Source.Name.1", "Source.Name.2"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Source.Name.2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Source.Name.1", "Source.Name"}}), #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Source.Name", "Source.Name - Copy"), #"Reordered Columns1" = Table.ReorderColumns(#"Duplicated Column",{"Index", "Content", "Source.Name", "Source.Name - Copy", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "Transform File"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Reordered Columns1", "Source.Name", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Source.Name.1", "Source.Name.2", "Source.Name.3"}), #"Renamed Columns2" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Source.Name.1", "Year"}, {"Source.Name.3", "Week"}, {"Source.Name - Copy", "Source.Name"}}), #"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},{"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"You were missing a ")," after the end of the anti join step.
Gotta close all your parenthesis and you need a comma if the step is not your last step.