Input data in a table and then refresh in another

h_gp9
Posts: 25
Joined: Wed Jan 18, 2017 9:50 am

Input data in a table and then refresh in another

Postby h_gp9 » Wed Sep 13, 2017 4:17 am

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
Attachments
PRECIO.xlsx
(344.06 KiB) Downloaded 13 times

MattAllington
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Input data in a table and then refresh in another

Postby MattAllington » Wed Sep 13, 2017 8:28 am

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?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

h_gp9
Posts: 25
Joined: Wed Jan 18, 2017 9:50 am

Re: Input data in a table and then refresh in another

Postby h_gp9 » Thu Sep 14, 2017 1:02 am

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
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Input data in a table and then refresh in another

Postby MattAllington » Thu Sep 14, 2017 7:42 am

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
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

h_gp9
Posts: 25
Joined: Wed Jan 18, 2017 9:50 am

Re: Input data in a table and then refresh in another

Postby h_gp9 » Thu Sep 14, 2017 9:40 am

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
Posts: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Input data in a table and then refresh in another

Postby MattAllington » Thu Sep 14, 2017 7:08 pm

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
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

h_gp9
Posts: 25
Joined: Wed Jan 18, 2017 9:50 am

Re: Input data in a table and then refresh in another

Postby h_gp9 » Tue Sep 19, 2017 3:03 am

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


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests