0

I really need some help on merging data in Power Query. I am importing data from a file (from a network drive) and also pulling data from a static table. However I am trying to obtain a match with headers 1) Container Number 2) ETA

Unfortunately the ETA's dont exactly match up, but im trying to put a rule which says Match if within +/- 5 days.

let
    Source = Table.NestedJoin(#"Allotrac Data", {"Container NO.", "VSL ETA"}, #"CAFS Data", {"container", "ETA"}, "CAFS Data", JoinKind.LeftOuter),
    #"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"

Something tells me I should be trying to use the Where function

ie Where 1=1 AND ETA Between GETDATE()-5 AND GETDATE ()+5

However I have no idea how to insert that (im very new to this)

Ive tried adding the above code and joining, but it doesnt seem to work. Im missing something

JDogg
  • 1
  • 1

1 Answers1

0

You can try something like this in powerquery. It replaces the merge step in Table2 with a custom column that checks if Something column is the same between the two tables, and that Date column in the two tables are within 5 days of each other

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Something", type text}, {"Date", type date}}),
// find matching rows from Table1 that has same Something, and Date within 5 days of each other
#"Added Custom" = Table.AddColumn( #"Changed Type", "Custom", each Table.SelectRows(Table1,(x) => [Something]=x[Something] and Number.Abs(Number.From([Date]-x[Date]))<5)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Something", "Date"}, {"Custom.Something", "Custom.Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Date", type date}})
in  #"Changed Type1"

enter image description here

for large data sources, Table.Buffer() Table2 before using it in the custom column

horseyride
  • 17,007
  • 2
  • 11
  • 22