Forum Discussion

Savino's avatar
Savino
Frequent Visitor
2 years ago
Solved

= Table.NestedJoin

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?

 

 

Need Help
  • spinfuzer's avatar
    spinfuzer
    2 years ago

    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.

     

13 Replies

  • spinfuzer's avatar
    spinfuzer
    Solution Sage
    2 years ago

     

    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's avatar
      Savino
      Frequent Visitor
      2 years ago

      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's avatar
        spinfuzer
        Solution Sage
        2 years ago
        #"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.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

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

    • Savino's avatar
      Savino
      Frequent Visitor
      2 years ago

      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.