Append new data into second table from the first.

[k3rz0rg]

Hi all, 

I was trying to implement an incremental load for the usage metrics report.

I have the excel table imported which gets refreshed bi-weekly (manually) and I have another table created to hold historical data since PBI generated usage metrics dump file holds only 90 days of data. 

I wanted to keep all previous data into the second table and update only new data from the first table.

Since the first table already have the previous 75 days of information (90days - 15 for bi weekly dump), simple append will end up creating duplicate rows. 

Is there any way to append with condition where only the rows will be appended from the first table where the date is after (lets say) 05/15/2018?

Can I put the condition in advance editor  (given below) for the second table?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Airbus}),
    #"Removed Columns" = Table.RemoveColumns(#"Appended Query",{"Column1"})
in
    #"Removed Columns"

I have another cheap idea where the tables will get appended and remove duplicates based on the dates, however, I am little skeptical about that.

Thanks,

K3

[v-jiascu-msft · Accepted solution]

Hi @k3rz0rg,

Your idea could be the best way. I guess you don't have duplicates in a single file. So,

1. Append the tables as a new table or what you need.

2. Select the columns that you think they can be the criterion.

3. Click remove duplicates.

Best Regards,

Dale

[k3rz0rg]

Hi @v-jiascu-msft,

thank you for the reply and supporting my idea. Rather than following the messy way, for now I'm just keeping it simple and doing the same thing as you showed.

Unfortunately, I do have multiple columns with duplicate values, for example one person has multiple report page views; what I did was 

• copy the eniter table
• enter data, ctrl + v
• append the new table with the original table (which creates double rows with duplicated data)
• selected user principal name, date, report page columns and clicked on remove duplicates.

Seems like it worked so far, have to wait for few more tests but I guess this steps might work for other people with same issue as well. I am marking your reply as answer since that gave me the hint of selecting multiple columns.

Regards,

K3