Look for a date in a Column List

You need a  column in the Payment table to merge against. Perhaps the Invoice number?

Please provide sample data, not screenshots. Paste the data in a table, or link to an Excel file via OneDrive so we don't have to rekey. Screenshots are nice to see expected output, but then you are expecting someone to rekey that info in to do the work.

Hi JoaoMS ,

in Power Query Editor follow theses steps:

// InvoiceTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1MFTSUTIw1Dc00DcyUIrVAYsZgcSM9A1NkcSMQWLG+kBhkFgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Invoice = _t, #"lnvoice Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice", Int64.Type}, {"lnvoice Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"lnvoice Date", type date}}, "en-US"),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"lnvoice Date", "Dates"}})
in
    #"Renamed Columns"

// PaymentTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA6VYHTDH0BTOMUKWMUKWMUaWMUaWMUGWMYHJxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FixedJDates = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"FixedJDates", type date}}, "en-US"),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"FixedJDates", "Dates"}})
in
    #"Renamed Columns"

// Append1
let
    Source = Table.Combine({InvoiceTable, PaymentTable}),
    #"Sorted Rows" = Table.Sort(Source,{{"Dates", Order.Ascending}}),
    #"Removed Top Rows" = Table.Skip(#"Sorted Rows",1),
    #"Filled Down" = Table.FillDown(#"Removed Top Rows",{"Invoice"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Invoice"}, {{"Count", each _, type table [Invoice=number, Dates=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FirstN([Count],2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Skip", each Table.Skip([Custom],1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Skip"}),
    #"Expanded Skip" = Table.ExpandTableColumn(#"Removed Other Columns", "Skip", {"Invoice", "Dates"}, {"Invoice", "Dates"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Skip",{{"Invoice", Int64.Type}, {"Dates", type date}})
in
    #"Changed Type"

// Merge1
let
    Source = Table.NestedJoin(Append1, {"Invoice"}, InvoiceTable, {"Invoice"}, "InvoiceTable", JoinKind.LeftOuter),
    #"Expanded InvoiceTable" = Table.ExpandTableColumn(Source, "InvoiceTable", {"Dates"}, {"Dates.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded InvoiceTable",{"Invoice", "Dates.1", "Dates"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Dates.1", "InvoiceDate"}, {"Dates", "PaymentDate"}})
in
    #"Renamed Columns"

Regards FrankAT