How to create Type 2 SCD table for price tracking

[jamuka]

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

[amitchandak · Accepted solution]

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

https://community.powerbi.com/t5/Desktop/Creating-a-summary-table-out-of-existing-table-assistance/t...

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-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[jamuka]

Hello @amitchandak,

it works thanks, I didn't thought I can use summarize or group by in Query Editor.

have a nice day

[amitchandak]

You can apply logic when Min date and max date is null make max date as null

[jamuka]

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