= Table.NestedJoin

[Savino]

Hi all,

I'm trying to write a formula to process the below list of files Query using Table.Nestedjoin Function (JoinKind.RightAnti).

 Table.Nestedjoin Function requires two tables: in my case the first row will be the first Table in the formula  and the second row  the second Table in the formula. Then the second and the third and so on.... resulting in 8 new "RightAnti" Tables.

I was able to make it manually, but I need to automate the process. Can you please help?

[wdx223_Daniel]

NewStep=List.Transform(List.Zip({List.RemoveLastN(PreivousStep[TransformFile]),List.Skip(PreivousStep[TransformFile])}),each Table.NestedJoin(_{0},ColumnList,_{1},ColumnList,"NewCol",JoinKind.RightAnti))

[Savino]

Hello Daniel, Thanks for your answer. Unfortunatly it didn't work. It is giving a new column with list item with error inside. See Below all details. Also I need a Table not a list.

Thanks again.

[spinfuzer]

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

[Savino]

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.

[Savino]

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"

[Savino]

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.

[Savino]

Hi, so: good news is that I've not sintax error anymore. Bad news is that as a result I got a List of empty tables. Now let's try do get the same result in an other way:

If I have this query

let
#"Service Order Trend Elaborato" = #"Elabora dato SAP" (#"Service Order Trend RAW"),
#"Removed Columns" = Table.RemoveColumns(#"Service Order Trend Elaborato",{"Instrument Model", "Instrument Serial Number", "Functional Loc.", "Region", "Postal Code", "City", "Street", "Telephone", "Index", "Overdue days", "OverDue 5 days", "OverDue 2 days"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Week Number", "Source Name"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"Week", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.EndOfWeek(Date.AddWeeks(#date([Year], 1, 1), [Week]
), Day.Monday)),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "End Week Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"End Week Date", type date}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Over Due Days Weekly based", each Networkingdays([Created on], [End Week Date], null)),
#"Added Custom3" = Table.AddColumn(#"Invoked Custom Function", "Weekly New Service Order", each if Date.AddDays([Created on],5) >= [End Week Date] then true else false),
#"Added Custom1" = Table.AddColumn(#"Added Custom3", "Overdue >5days", each if [Over Due Days Weekly based] > 5 then true else false)
in
#"Added Custom1"

can I add a new column named "Closed Service Order" in the table that looking at "Week Number" Column will put value "TRUE" if one "Order" (in the column named "Order")  contained in the current week  is not present in the next week?

For example for week 50 in the new column named "Closed Service Order" will have a TRUE value when order in week 50 will not show in week 51

What is the formula I shoud add?

Maybe since, in this case, I've already and expanded whole table is easier to get the result I'm looking for.

I really appreciate your help.

Regards

[spinfuzer · Accepted solution]

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.

[Savino]

Finally I see the light!!! Many Thanks!!!!

Now I need to figure out:

1) how to exclude the last week data since  data are inconsistent because they have not a matching week yet;

2) MAX Week doesn't work for the new year. I mean after week 52 (2023) there's week 01 (2024) . I tried to put MAX on Source.Name that is (as example) 2023-W-52 but it doesn't work.

I need to think of it.

Thanks again for your help. I coundn't never achieve this result without your help.

Savino

[spinfuzer]

Probably just concatenate year with week number.  202401 > 202352

[spinfuzer]

remove the #"Added Custom"=

your step name is anti_join_list.