none
Excel Power Query Function RRS feed

  • Question

  • I currently have a data set which contains 3 columns Customer Account Numbers, The Date a payment was made and the final column is the amount that was paid. I need a function that can check the previous payment and tell me the difference next to the current payment and if no previous payment then give current payment.

    (In excel I would have sorted the data by Customer By Date and checked the row above if the Customer ID was the same then deducted current row by row above to get difference else keep current row) 

    See example below

    Customer Account               Date                            Amount           Difference (Worked out by FUnction)

    0000000001                       01/08/2018                 £300                £300 (As there is no previous payment)

    0000000002                       03/08/2018                 £250                £250 (As there is no previous payment)

    0000000001                       10/08/2018                 £700                £400 (as 700-300 = 400)

    0000000001                       11/08/2018                 £1000              £300 ( as 1000-700=300)




    • Edited by Emkay79 Thursday, July 19, 2018 7:14 PM
    Thursday, July 19, 2018 7:11 PM

Answers

  • Sorry I wasn’t clear enough I sort by date and ascending payments ... every subsequent payment is an accumulative so if 300was paid on day 1 and another payment was made on the same day it would be higher than 300 as it would be inclusive of the payment before. Hope that helps..
    • Marked as answer by Emkay79 Friday, July 20, 2018 8:29 PM
    Friday, July 20, 2018 8:28 PM

All replies

  • You say "In excel I would have sorted the data by Customer By Date". What would happen if the same customer makes 2 or more payments on the same day? 

    Does the order of the data come in the order that it was entered? 

    It would good to get some clarity on this edge case as stable sorting may be required if it is that the source data is somewhat but not entirely ordered.  

    Thursday, July 19, 2018 10:40 PM
  • Sorry I wasn’t clear enough I sort by date and ascending payments ... every subsequent payment is an accumulative so if 300was paid on day 1 and another payment was made on the same day it would be higher than 300 as it would be inclusive of the payment before. Hope that helps..
    • Marked as answer by Emkay79 Friday, July 20, 2018 8:29 PM
    Friday, July 20, 2018 8:28 PM
  • Sorry I guess I would need a way to identify if the subsequent payment was higher ... as there could be human error in the inputting for example the agent may put in wrong accumulative amount. So day 1 they offer 1000 and day ten they offer another £100 then the accumulative amount should be 1100 they input but they might type in 110 in these instances you would should -990 and we would exception report this and feedback
    Friday, July 20, 2018 8:33 PM
  • Here is the query. Source will need to be changed.
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
       //ColumnNames
       ,AccNo = "Customer Account"
       ,Date = "Date"
       ,Amount = "Amount"
       //
       ,ChangeTypes = Table.TransformColumnTypes(Source,{{AccNo, type text},{Date, type date},{Amount, Currency.Type}})
       ,Sort = Table.Sort(ChangeTypes, {{Date, Order.Ascending}, {Amount, Order.Ascending}}) 
       ,fAddNewCol = (Table as table) =>
        let
            NewCol = Value.ReplaceType({0}&List.RemoveLastN(Table.Column(Table,Amount)),type {Currency.Type})
           ,NewTable = Table.FromColumns(Table.ToColumns(Table)&{NewCol},NewColNames)
        in
            NewTable
       ,NewColNames = List.Buffer(Table.ColumnNames(Source)&{"Prev.Amount"})
       ,Group = Table.Group(Table.Buffer(Sort),{AccNo},{{"Table", each fAddNewCol(_)}})
       ,Combine = Table.Combine(Group[Table]),
        AddDifference = Table.AddColumn(Combine, "Difference", each Record.Field(_,Amount)-_[Prev.Amount],Currency.Type)
    in
        AddDifference

    Saturday, July 21, 2018 12:54 AM
  • Hi as I am new to this can you provide some commentary around the code so I can understand it better... assume I know nothing. Please :)
    Saturday, July 21, 2018 6:42 AM
  • I have changed the code somewhat and added code comments. Also, one of the great things about PowerQuery is that you can click through the "Applied Steps" in the right hand pane which will show the table getting transformed. 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
       //ColumnNames
       ,AccNo = "Customer Account"
       ,Date = "Date"
       ,Amount = "Amount"
       //
       ,ChangeTypes = Table.TransformColumnTypes(Source,{{AccNo, type text},{Date, type date},{Amount, Currency.Type}})
       ,Sort = Table.Sort(ChangeTypes, {{AccNo, Order.Ascending}, {Date, Order.Ascending}, {Amount, Order.Ascending}}) 
       ,fAddNewCol = (Table as table) =>
        let
            NewColNames = List.Buffer(Table.ColumnNames(Source)&{"Prev.Amount"}) //List.Buffer is used to speed things up
           ,Amount = Table.Column(Table,Amount) //Turns column into list of its values
           ,PrevAmount = {0}&List.RemoveLastN(Amount,1) 
           ,PrevAmountAsCurrency = Value.ReplaceType(PrevAmount,type {Currency.Type}) //Setting type of new column
           ,NewTable = Table.FromColumns(Table.ToColumns(Table)&{PrevAmountAsCurrency},NewColNames)
            //Table.ToColumns function turns each column into a list of its values, and those lists are put together into another list.
            //Table.FromColumns function is the opposite of Table.ToColumns function.   
        in
            NewTable
       ,Group = Table.Group(Table.Buffer(Sort),{AccNo},{{"Table", each fAddNewCol(_)}}, GroupKind.Local) 
        /*Use of GroupKind.Local: 
          Ordered on AccNo first in Sort step to make it clear what was happening when clicking though applied steps
          GroupKind.Local has Table.Group go down the key column(s) (in this case AccNo) in a consecutive fashion 
          and it splits where AccNo changes. As table had already been ordered on AccNo, can then use 
          GroupKind.Local which is more efficient and thus faster.*/
        /*Use of Table.Buffer - Table.Group function often ignores Table.Sort function in previous steps
          and Table.Buffer stops this unwanted behaviour.
        /*Another way of making this query would be Sort each individual nested table after you have used Table.Group, 
          (and then GroupKind.Local and Table.Buffer wouldn't be needed). 
          However I think this way is clearer when clicking through the "Applied Steps" */      
       ,Combine = Table.Combine(Group[Table]),
        AddDifColm = Table.AddColumn(Combine, "Difference", each Record.Field(_,Amount)-_[Prev.Amount],Currency.Type)
    in
        AddDifColm

    Saturday, July 21, 2018 11:04 PM
  • Thankyou so much I will step through the applied steps.
    Sunday, July 22, 2018 10:39 PM