Calculate Difference previous date in Power Query ( M ) (only)

[Jmenas]

Hi,

I have to calculate the cost from today the previous day difference with the last date. I want to do it in Power Query Language (M) because we have to merge a lot of tables, so DAX is not an option. 

The issue is that I tried using the index function like this (from here😞

= #"Added Index"{[Index]-1}[My Column]

But I had no luck in creating a conditional option that helps me to get the item when there is more one item on the same date. This my test data as you can see I have products (A, B and C) and I need the costs difference between today and yesterday. For example, product A is $120 (29-05-17) - $110 (28-05-17) = $10. 



Thanks and regards,
J.

[Jmenas]

@MarcelBeug This is the topic that is related to power Query, Thanks 

[MarcelBeug]

@Jmenas 2 questions:

Can 1 product have multiple cost records on 1 date and if so, which cost should apply as cost or the previous date?

What should be the "difference" for the first date?

[Jmenas]

Hi @MarcelBeug, 

regarding the first question, the products have only one cost by date. The second is a bit more complicated, for the first date, there is no expected value because you can't compare to a previous value.  Probably this value will be deleted (so doesn't cause an error).

Thanks,

Best,
J

[MarcelBeug · Accepted solution]

Find below 2 queries.

The first query is the proposed solution.

This includes a function which you can't step through in the query editor.

The second query is an amended vesion for 1 product, with some lines commented out and some other lines added, so you can see the steps that are part of the function in the first query.

FYI: I first created the query for 1 product and turned this into a function for the final solution.

I tested with about 25,000 rows of data in Excel and it runs for about 2 seconds (!).

Proposed solution:

let
    Source = TestData,
    #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"AllData", each fnPriceDifference(_), type table}}),

    fnPriceDifference = (MyTable as table) as table =>
    let
        #"Sorted Rows" = Table.Buffer(Table.Sort(MyTable,{{"date", Order.Descending}})),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"PreviousDate",JoinKind.LeftOuter),
        #"Expanded PreviousDate" = Table.ExpandTableColumn(#"Merged Queries", "PreviousDate", {"Cost"}, {"PreviousDate.Cost"}),
        #"Added Custom" = Table.AddColumn(#"Expanded PreviousDate", "Difference", each if [PreviousDate.Cost] = null then null else [Cost] - [PreviousDate.Cost]),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "PreviousDate.Cost"})
    in
        #"Removed Columns",

    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"date", "Campaign", "Cost", "id", "Difference"}, {"date", "Campaign", "Cost", "id", "Difference"})
in
    #"Expanded AllData"

Code for 1 product only (just to see all steps):

let
    Source = TestData,
//    #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"AllData", each fnPriceDifference(_), type table}}),
    #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"AllData", each _, type table}}),
    MyTable = #"Grouped Rows"{[Product="A"]}[AllData],

//    fnPriceDifference = (MyTable as table) as table =>
//    let
        #"Sorted Rows" = Table.Buffer(Table.Sort(MyTable,{{"date", Order.Descending}})),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"PreviousDate",JoinKind.LeftOuter),
        #"Expanded PreviousDate" = Table.ExpandTableColumn(#"Merged Queries", "PreviousDate", {"Cost"}, {"PreviousDate.Cost"}),
        #"Added Custom" = Table.AddColumn(#"Expanded PreviousDate", "Difference", each if [PreviousDate.Cost] = null then null else [Cost] - [PreviousDate.Cost]),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "PreviousDate.Cost"})
//    in
//        #"Removed Columns",

//    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"date", "Campaign", "Cost", "id", "Difference"}, {"date", "Campaign", "Cost", "id", "Difference"})
in
//    #"Expanded AllData"
    #"Removed Columns"

[Jmenas]

Hi @MarcelBeug,

Your code helped me to resolve the issue. I just had to add a couple of extra steps to sort the rows correctly.

Thanks,
J