Power Query中基于集装箱号和±5天ETA合并数据的技术求助

修改思路 #

原Table.NestedJoin仅支持等值匹配，无法实现日期范围筛选。我们需要替换初始连接步骤，改为为每一行主表数据筛选符合条件的子表数据：

• 匹配条件1：Container NO. = container（集装箱号完全一致）
• 匹配条件2：CAFS Data的ETA在Allotrac Data的VSL ETA的±5天范围内

修改后的完整代码 #

let
    // 替换原Source步骤：自定义筛选嵌套符合条件的CAFS数据
    Source = Table.AddColumn(#"Allotrac Data", "CAFS Data", (row) => 
        Table.SelectRows(#"CAFS Data", 
            each [container] = row[Container NO.] 
            and [ETA] >= Date.AddDays(row[VSL ETA], -5) 
            and [ETA] <= Date.AddDays(row[VSL ETA], 5)
        )
    ),
    #"Expanded CAFS Data" = Table.ExpandTableColumn(Source, "CAFS Data", {"vessel", "voyage", "ETA", "Discharged", "GateOut", "ISOCODE"}, {"CAFS Data.vessel", "CAFS Data.voyage", "CAFS Data.ETA", "CAFS Data.Discharged", "CAFS Data.GateOut", "CAFS Data.ISOCODE"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded CAFS Data",{"Source.Name", "S.No", "Job Id", "Job Reference", "Job Date", "Client", "Supplier / Pickup Point", "Customer / Delivery point", "Lot ID", "Inventory ID", "Products", "Volume", "Pickup Date/Time", "Delivery Date/Time", "Delivery Type", "Created By", "Created Date/Time", "Updated By", "Updated Date/Time", "Total Price", "Fleet", "Subcontractor", "Vehicle Rego", "Driver", "Salesperson", "Total Weight", "Assigned Weight", "Delivered Weight", "Price Description", "Job Status", "Comments", "Subbie Rate", "Site Inspection", "Container NO.", "Customs Entry", "CUST PO", "Docket #", "Vehicle Identifier", "SHIFT", "VSL ETA", "ECN", "VSL Name", "PIN TSW", "VBS #", "VBS Date", "DMR LFD", "DET LFD", "Cust Notes", "SHIP Line", "RAND KI", "Service", "Doors", "Grid", "Driver Notes", "BILL CLIENT", "Customs", "MPI HOLD", "SHIP HOLD", "MON HOLD", "PLAN HOLD", "Delivery Date", "HSW", "Broker", "DG Class", "Equipment Type", "CAFS consol", "Unpack consol", "Special Handling", "LOCATION", "ATA", "AVAILABLE", "OPERATIONAL COMMENTS", "Storage", "Reuse", "FullMT", "CHEP DEL", "MPLT DEL", "Master Bill", "DAMAGED", "CHEP RET", "MPLT RET", "REPRICE", "Dehire Flag", "Parent", "Xero Invoice", "Xero User", "Xero Date", "MYOB Invoice", "MYOB User", "MYOB Date", "CSV Invoice User", "CSV Invoice Date", "CAFS Data.ISOCODE", "CAFS Data.vessel", "CAFS Data.voyage", "CAFS Data.ETA", "CAFS Data.Discharged", "CAFS Data.GateOut"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"CSV Invoice User", "CSV Invoice Date"})
in
    #"Removed Columns"

关键说明 #

1. 日期类型校验：确保VSL ETA和ETA列是日期类型，如果是文本格式，需要先添加转换步骤，比如：

   // 转换Allotrac Data的VSL ETA为日期
   #"Converted VSL ETA" = Table.TransformColumns(#"Allotrac Data", {{"VSL ETA", Date.From, type date}}),
   // 转换CAFS Data的ETA为日期
   #"Converted CAFS ETA" = Table.TransformColumns(#"CAFS Data", {{"ETA", Date.From, type date}})
   

   然后在后续步骤中使用转换后的表名。

2. 筛选逻辑解析：(row) =>表示对主表的每一行进行操作，Table.SelectRows筛选子表中满足两个条件的行：

   • 集装箱号完全匹配
   • 子表ETA在主表ETA减5天到加5天的区间内

3. 左连接效果保留：这种方式保留了左外连接的特性——主表所有行都会保留，符合条件的子表数据会展开，无匹配的行会显示空值。

内容的提问来源于stack exchange，提问作者JDogg