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
Input data in a table and then refresh in another
Input data in a table and then refresh in another
- Attachments
-
- PRECIO.xlsx
- (344.06 KiB) Downloaded 46 times
-
- Posts: 929
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: Input data in a table and then refresh in another
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?
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 Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/
https://exceleratorbi.com.au/power-bi-online-training/
Re: Input data in a table and then refresh in another
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"?
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"?
-
- Posts: 929
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: Input data in a table and then refresh in another
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
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 Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/
https://exceleratorbi.com.au/power-bi-online-training/
Re: Input data in a table and then refresh in another
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
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
-
- Posts: 929
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: Input data in a table and then refresh in another
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 Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/
https://exceleratorbi.com.au/power-bi-online-training/
Re: Input data in a table and then refresh in another
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
Still I have doubt if is possible make this without add columns. but your suggestions for now are working.
Thank you for your suggestions
Who is online
Users browsing this forum: Google [Bot] and 3 guests