Input data in a table and then refresh in another

[h_gp9]

I need to refresh "PreviousPrice" table with "NewPrice" table only with data appears in "New Price" without alter another lines.
Fields to update: [PO Price] , [Freight]
After that I Need to recalculate other columns such:
[Unit Act] Will be equals to = [PO Price] + [Freight]
[Total Actual] Will be equals to = [Unit Actual] * [Usage]
[Act Amount] Will be equals to = [Total Actual] * [Qty]

How do I get this with Power Query?

Thanks in advanced

[MattAllington]

Power Query can process data, but it is generally not ideal for creating data. As far as I can tell from your example, you have 2 tables. One is "current price" (you call it previous price) and one is a new price. These 2 sets of data are in 2 tables. What happens when the new price becomes the current price and then in 12 months time there is another new price? Do you then have 3 tables? This seems like an issue to me.

have you considered having a single table that contains the price information. Add columns for Start Date and End Date like this (International standard date formats).

Prod Start End
A 1/1/2017 30/4/2017
A 1/5/2017 9/9/9999

That way you can extract the current price. Each time the price changes you just update the table.

Can that work?

[h_gp9]

Your appreciation is useful.
The key for update will be [Material Number], mainly. I need if there is a [Material Number] in both tables then one refresh to another one (New to Current)
1.- How do I refresh "CurrentPrice" based only in effective rows from "NewPrice" including date as you suggest?
2.- After refresh How can I recalculate other fields based in "NewPrice"?

[MattAllington]

If you want to keep 2 tables, you can start with the current price table, then in Power Query perform a merge with the second table using material number (left outer join). Expand the new table column to extract the new price, then write a custom column something like this

If [newprice] = null then [old price] else [newprice]

Delete both old price and new price columns, then rename the custom column to the same name as the old price column

[h_gp9]

Dear Matt:

I'll try with your method and I'll let you know.

We take in account that only we are refresh one column [PO Price] , how about if we need refresh 10 or more colums?
There is no a formula such as "RecordFromTable" or something like that in order to avoid add columns and then delete it.

Thanks in advanced

[MattAllington]

Well the way I would do it is still the way I originally mentioned. Create a table that contains all the details and use time stamps to show which are active. Effort spent up front redesigning the data structure is paid back via simplified reporting

[h_gp9]

I've done with successful the query that I wanted.
Still I have doubt if is possible make this without add columns. but your suggestions for now are working.

Thank you for your suggestions