Using Corrections to overwrite historical data

[waitehamrick]

Hi, 

I have two table (Historical Data & Corrections). I need to load all the historical data and if there are corrections I need to be able to overwrite the Historical Data where the corrections happened.

See example below: 

Example

[jennratten · Accepted solution]

Hello - this is one way you can accomplish the result - add a date column to both tables for the date the row is added to the table.  Then append the tables and group by the max date for each like so.

Historical Data table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEyNDI2MQXRQAwVMjUwUIrVwVBlhqrKHLsqc1RVJiBVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, SSN = _t, Month = _t, Year = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Currency.Type}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"SSN", Int64.Type}, {"Date", type date}})
in
    #"Changed Type"

Corrections table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30jcyMDJS0lEyNDI2MQXRQAwVMjYwUIrVwVBlhqrKCKQqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, SSN = _t, Month = _t, Year = _t, Amount = _t]),
    Custom1 = Table.TransformColumnTypes(Source,{{"Amount", Currency.Type}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"SSN", Int64.Type}, {"Date", type date}})
in
    Custom1

New Table

New Table script:

let
    Source = Table.Combine({#"Historical Data", Corrections}),
    GroupedRows = Table.Group(Source, {"SSN", "Year", "Month"}, {{"Data", each _, type table}}),
    LatestDate = Table.TransformColumns(
        GroupedRows,
        {
            "Data",
            (t) => Table.SelectRows ( t, let MaxDate = List.Max ( t[Date] ) in each [Date] = MaxDate )
        }
    ),
    #"Expanded Data" = Table.ExpandTableColumn(LatestDate, "Data", {"Amount", "Date"})

in
    #"Expanded Data"

[waitehamrick]

Thank you!