none
Determine End Date from Price change date RRS feed

  • Question

  • hello,

    I have a table for purchase price of products for stores where I get from SAP BW and  I want to change it to an SCD table. So I can reduce my file size.  My power query knowledge is limited, I only use it for simple ETL and have no idea whether it can be done or not.

    my current data is

    Store Product Purchase Price Date
    A 12 4 1.11.2019
    A 12 4 2.11.2019
    A 12 5 4.12.2019
    B 12 4 1.11.2019
    B 12 4 2.11.2019
    B 12 5,2 6.12.2019

    and I would like to have it as


    Store Product Purchase Price Start Date End Date
    A 12 4 1.11.2019 3.12.2019
    A 12 5 4.12.2019  
    B 12 4 1.11.2019 5.12.2019
    B 12 5,2 6.12.2019  

    kind regards



    • Edited by jamuka Tuesday, December 31, 2019 5:27 AM
    Saturday, December 28, 2019 10:07 PM

Answers

  • Hi

    You can do it with the UI only then: select columns [Store],[Product] and [Purchase Price] > Right-click > Group By... > Add 2 aggregations: Min of [Date], Max of [Date]

    corresponding code (still with Table1 as source):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", Int64.Type}, {"Product", type number}, {"Purchase Price", type number}, {"Date", type date}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Store", "Product", "Purchase Price"}, {{"Start Date", each List.Min([Date]), type date}, {"End Date", each List.Max([Date]), type date}})
    in
        #"Grouped Rows"

    • Marked as answer by jamuka Monday, December 30, 2019 5:17 AM
    Monday, December 30, 2019 4:11 AM

All replies

  • Hi

    Based on your sample and assuming source data in Excel TableSource, one way:

    let
        Source = Excel.CurrentWorkbook(){[Name="TableSource"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Store", type text}, {"Product", type number}, {"Purchase Price", type number}, {"Date", type date}}),
        RenamedDate = Table.RenameColumns(ChangedTypes,{{"Date", "Start Date"}}),
        GroupedRows = Table.Group(RenamedDate, {"Store", "Product"}, {{"GRP", each _, type table}}),
        TransformedGroup = Table.TransformColumns(GroupedRows,
            {"GRP", (state)=>
                let
                    minStart = List.Min(state[Start Date]),
                    maxDate = List.Max(state[Start Date]),
                    endDate = Table.AddColumn(state,"End Date", each
                        if [Start Date] = minStart
                        then Date.AddDays(maxDate,-1)
                        else null
                    )
                in
                    Table.SelectRows(endDate, each ([Start Date]=minStart) or ([Start Date]=maxDate))
            }
        ),
        ExpandedGroup = Table.ExpandTableColumn(TransformedGroup, "GRP", {"Purchase Price", "Start Date", "End Date"}),
        ChangedTypes1 = Table.TransformColumnTypes(ExpandedGroup,{{"Purchase Price", type number}, {"Start Date", type date}, {"End Date", type date}})
    in
        ChangedTypes1

    Sunday, December 29, 2019 8:35 AM
  • Hello Lz._,

    thank you for your reply, I insert your code but instead of showing each price change in a new row it shows two lines. one starts from min date and the second from max date. below you can find the code. As I understand in your code you are grouping based on store and product, but I also have to track Purchase Price changes too. I need all price changes between min and max date.


    result data from power query is

    Store Product Purchase Price           Start Date           End Date
    8107 9660 13,36 12.01.2019 21.12.2019
    8107 9660 12,05 22.12.2019

    and sample from my source is

    Store Product Purchase Price            Date
    8107 9660 13,36 12.01.2019
    8107 9660 13,36 29.03.2019
    8107 9660 13,42 6.04.2019
    8107 9660 13,71 7.04.2019
    8107 9660 13,71 9.04.2019
    8107 9660 14,05 10.04.2019
    8107 9660 14,05 11.04.2019
    8107 9660 14,17 12.04.2019
    8107 9660 14,17 14.04.2019
    8107 9660 14,62 15.04.2019
    8107 9660 14,62 16.04.2019
    8107 9660 14,61 17.04.2019
    8107 9660 14,61 23.04.2019
    8107 9660 14,78 24.04.2019
    8107 9660 14,78 28.04.2019
    8107 9660 14,88 29.04.2019
    8107 9660 14,88 3.05.2019
    8107 9660 14,95 4.05.2019
    8107 9660 14,95 9.05.2019
    8107 9660 14,98 10.05.2019
    8107 9660 14,98 15.05.2019
    8107 9660 15,01 16.05.2019
    8107 9660 15,01 22.05.2019
    8107 9660 15,03 23.05.2019
    8107 9660 15,03 28.05.2019
    8107 9660 15,05 29.05.2019

    let
        Source = Excel.Workbook(File.Contents("C:\DATA\Desktop\TableSource.xlsx"), null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Store", type text}, {"Product", Int64.Type}, {"Purchase Price", type number}, {"Date", type date}}),
        RenamedDate = Table.RenameColumns(#"Changed Type",{{"Date", "Start Date"}}),
        #"Sorted Rows" = Table.Sort(RenamedDate,{{"Start Date", Order.Ascending}}),
        GroupedRows = Table.Group(#"Sorted Rows", {"Store", "Product"}, {{"GRP", each _, type table [Store=text, Product=number, Purchase Price=number, Start Date=date]}}),
        TransformedGroup = Table.TransformColumns(GroupedRows,
                   {"GRP", (state)=>
                let
                    minStart = List.Min(state[Start Date]),
                    maxDate = List.Max(state[Start Date]),
                    endDate = Table.AddColumn(state,"End Date", each
                        if [Start Date] = minStart
                        then Date.AddDays(maxDate,-1)
                        else null
                    )
                in
                    Table.SelectRows(endDate, each ([Start Date]=minStart) or ([Start Date]=maxDate))
            }
        ),
        ExpandedGroup = Table.ExpandTableColumn(TransformedGroup, "GRP", {"Purchase Price", "Start Date", "End Date"}),
        ChangedTypes1 = Table.TransformColumnTypes(ExpandedGroup,{{"Purchase Price", type number}, {"Start Date", type date}, {"End Date", type date}})
    in
        ChangedTypes1

    Sunday, December 29, 2019 6:43 PM
  • ...but I also have to track Purchase Price changes too

    Why didn't you mention this in your initial request (no crystal ball here)?

    What is the expected result from your above data?

    Sunday, December 29, 2019 7:59 PM
  • above data should be as below

    Store Product Purchase Price Start Date End Date
    8107 9660 13,36 12.01.2019 29.03.2019
    8107 9660 13,42 6.04.2019 6.04.2019
    8107 9660 13,71 7.04.2019 9.04.2019
    8107 9660 14,05 10.04.2019 11.04.2019
    8107 9660 14,17 12.04.2019 14.04.2019
    8107 9660 14,62 15.04.2019 16.04.2019
    8107 9660 14,61 17.04.2019 23.04.2019
    8107 9660 14,78 24.04.2019 28.04.2019
    8107 9660 14,88 29.04.2019 3.05.2019
    8107 9660 14,95 4.05.2019 9.05.2019
    8107 9660 14,98 10.05.2019 15.05.2019
    8107 9660 15,01 16.05.2019 22.05.2019
    8107 9660 15,03 23.05.2019 28.05.2019
    8107 9660 15,05 29.05.2019 2.06.2019

    regards

    Sunday, December 29, 2019 10:35 PM
  • Hi

    You can do it with the UI only then: select columns [Store],[Product] and [Purchase Price] > Right-click > Group By... > Add 2 aggregations: Min of [Date], Max of [Date]

    corresponding code (still with Table1 as source):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", Int64.Type}, {"Product", type number}, {"Purchase Price", type number}, {"Date", type date}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Store", "Product", "Purchase Price"}, {{"Start Date", each List.Min([Date]), type date}, {"End Date", each List.Max([Date]), type date}})
    in
        #"Grouped Rows"

    • Marked as answer by jamuka Monday, December 30, 2019 5:17 AM
    Monday, December 30, 2019 4:11 AM
  • thank you for your help. I have another question, as you can realize my Power Query knowledge is limited. Can you give me some advice where/how to learn it?

    kind regards

    Monday, December 30, 2019 5:16 AM
  • my Power Query knowledge is limited. Can you give me some advice where/how to learn it?

    Hi

    Before, would you mind changing the title of your initial post so it better reflects what's finally been addressed? Something like: Determine End Date from Price change date - Thanks for others

    • Power Query "documentation" is here
    • Look at ImkeF Learning resources page
    • Search this forum and when you find usefull info don't forget to UpVote the corresponding reply/ies
    Monday, December 30, 2019 11:30 AM
  • Hello,

    thanks for tips, I updated title and will look the sources.

    regards

    Tuesday, December 31, 2019 5:39 AM