Forum Discussion
How to create Type 2 SCD table for price tracking
hello all,
I have a file that shows my purchase price on store level and daily basis , which includes a lot of duplicates because of the day stamp.
I want to to turn it a new table so I can reduce my file size, current file have over 9 milllion rows. But I couldn't find a solution in power query how to get data from next row.
below you can see my current file and desired result.
kind regards
current data file
desired result
Use summarize
Group on the store , product and purchase and take min and mac for date
SUMMARIZE(Price,Price[Store],Price[Product],Price[Price],"Min date",Min(Price[Date]),"Max date",Max(Price[Date]))Refer
https://community.powerbi.com/t5/forums/replypage/board-id/power-bi-designer/message-id/424476
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
4 Replies
Use summarize
Group on the store , product and purchase and take min and mac for date
SUMMARIZE(Price,Price[Store],Price[Product],Price[Price],"Min date",Min(Price[Date]),"Max date",Max(Price[Date]))Refer
https://community.powerbi.com/t5/forums/replypage/board-id/power-bi-designer/message-id/424476
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601You can apply logic when Min date and max date is null make max date as null
hello amitchandak,
sorry but I don't understand your solution. Each product have Min date, there's no data which don't have a min date.
regards
Hello amitchandak,
it works thanks, I didn't thought I can use summarize or group by in Query Editor.
have a nice day
