Append and update data

[karlosdsouza]

I have a scenerio where i get updated list of Products, owner, version, Product Manager etc for the month. The list contains only the active products. I have to build analytics based both active and inactive products and have to consider the most recent entries. i.e. suppose in Apr-18, there is a product 'A' which has version 1.01 and in July the Product A has version updated as version 1.04 and also contains a product AA2. I want to append the Apr-18 data in such a manner so that new line items corresponding to product AA2 gets added and version is updated for Product A as 1.04. How can i do it ?

[TomMartens]

Hey,

basically this scenario you are looking for is currently not supported by Power BI, as soon as dataflows become general available, they maybe will provide a solution. If you are wondering what this dataflows thing is this webinar might be of interest to you: https://powerbi.microsoft.com/en-us/blog/webinar-10-23-advanced-data-prep-with-power-bi-dataflows-fo...

For now you have to import each list, every time you receive a new list, this means the number of lists is growing, updating the version number of product A can become somewhat complicated, if this does not mean;

• keep the latest version of product A and purge all previous version

Besides that you have to develop some more sophisticated M queries, e.g. updating previous reords of product A with a validuntil date, as this is quite common for "Slowly Changing Dimension - type 2" dimension modeling.

Maybe you might consider to create sample data, like list1, list2 ... upload this data to onedrive and dropbox and share the link. And it also very helpful to create an Excel file that contains your expected result, where the result reflects different steps, for processing list1, and processing list2 (meaning list1 and list2), ...

Regards,

Tom

[karlosdsouza]

Thanks @TomMartens

or i could append data so that i have recent data on top and old month tables at bottom. Then i can simply remove duplicates. Since remove duplicate works from bottom to top, i will have older data removed and kept version updated version 1.03 and new products AA2 as it will not be duplicate.

[v-chuncz-msft · Accepted solution]

@karlosdsouza,

You may try Append Queries (Table.Combine) in Query Editor and refer to the Power Query solution in this post.