cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k3rz0rg
Advocate III
Advocate III

Append new data into second table from the first.

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

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft
Microsoft

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.

Append_new_data_into_second_table_from_the_first

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jiascu-msft
Microsoft
Microsoft

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.

Append_new_data_into_second_table_from_the_first

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors