none
% Difference to earlier date | Power Query RRS feed

  • Question

  • Hello.

    Imagine you have a table with dates. On each date you have a city name and lets say you have for each date and city name an information about the rainfall in mm.

    I want to compare each day with the day, respectively to the same city, before.

    This would look like this:

    Date  | City | mm | Diff. in %

    22.11 | Paris | 10 | x

    22.11 | New York | 11,9 | y

    22.11 | Tokyo | 16,4 | z

    23.11 | Paris | 14,3 | 143 %

    23.11 | New York | 8,7 | 73%

    23.11 | Tokyo | 12 | 73 %

    My Problem |

    I have no idea how to compare the results with the one before (not even in a pivot table). I there a smart way to do it in Power Query?

    Less code is better for me.

    Do someone know a way? Maybe I am missusing the functionality of power Query on this one?

    Thank you for your time.

    Laurent

    Wednesday, November 7, 2018 7:27 PM

Answers

  • Hi Laurent

    Someone will probably suggest something more efficient (see thread How can I do calculation based on next/prior row in Power Query? re. large dataset). In the meantime my 2 cents contrib. (I assumed 100% when there was no previous mm - easy to change):

    Query:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"City", type text}, {"mm", type number}}),
        #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Date", Order.Ascending}})),
        #"Added Previous" = Table.AddColumn(#"Sorted Rows", "Previous", 
            (i) => Table.LastN(Table.SelectRows(#"Sorted Rows", each ([City]=i[City] and [Date]<i[Date])), 1)[mm]{0}
                                         ),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Previous", {{"Previous", null}}),
        #"Added Custom" = Table.AddColumn(#"Replaced Errors", "Diff. in %", each if [Previous] = null then [mm]/[mm] else [mm]/[Previous], Percentage.Type),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "City", "mm", "Diff. in %"})
    in
        #"Removed Other Columns"

    Wednesday, November 7, 2018 10:17 PM
  • If you just want to compare to the previous date, then adding a helper column and doing a Merge operation would be the most efficient way to go:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzLSNzIwtFDSUVIISCzKLFYAsgwNlGJ10GX9UssVIvOLssEKDPUssSgJyc+uzAfLm+mZwOWNsVhgomeMRR7ZCgs9cywqEDYYKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date  " = _t, #" City " = _t, #" mm " = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date  ", type date}, {" City ", type text}, {" mm ", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "PreviousDate", each Date.AddDays([#"Date  "], -1)),
        #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"PreviousDate", " City "},#"Added Custom",{"Date  ", " City "},"Added Custom",JoinKind.LeftOuter),
        #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {" mm "}, {" mm .1"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"PreviousDate"})
    in
        #"Removed Columns"

    the cool part is that all of this can be done via the UI.

    Tuesday, November 13, 2018 1:34 PM

All replies

  • Hi Laurent

    Someone will probably suggest something more efficient (see thread How can I do calculation based on next/prior row in Power Query? re. large dataset). In the meantime my 2 cents contrib. (I assumed 100% when there was no previous mm - easy to change):

    Query:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"City", type text}, {"mm", type number}}),
        #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Date", Order.Ascending}})),
        #"Added Previous" = Table.AddColumn(#"Sorted Rows", "Previous", 
            (i) => Table.LastN(Table.SelectRows(#"Sorted Rows", each ([City]=i[City] and [Date]<i[Date])), 1)[mm]{0}
                                         ),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Previous", {{"Previous", null}}),
        #"Added Custom" = Table.AddColumn(#"Replaced Errors", "Diff. in %", each if [Previous] = null then [mm]/[mm] else [mm]/[Previous], Percentage.Type),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "City", "mm", "Diff. in %"})
    in
        #"Removed Other Columns"

    Wednesday, November 7, 2018 10:17 PM
  • You can remove the #Replace Errors" step by modifying the #"Added Custom" step:

    #"Added Custom" = Table.AddColumn(#"Added Previous", "Diff. in %", each try if [Previous] = null then [mm]/[mm] else [mm]/[Previous] otherwise null, Percentage.Type),
    

    Thursday, November 8, 2018 7:00 PM
  • Hi Laurent

    You can do it just with one line. Not recomended, it's obscure and stupid, but you can.

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
        NewCol = Table.AddColumn(Source, "Diff. in %", (r1) => try r1[mm]/Table.Last(Table.SelectRows(Source, (r2) => r1[City] = r2[City] and Table.PositionOf(Source,r1) > Table.PositionOf(Source, r2)))[mm] otherwise 1)
    in
        NewCol

    For each record of the Source table, it takes the Source table, filter it by city and position of each record, then takes the last record and gets de value of the "mm" field. The "mm" field of the current record is divided by the value of the same field of the previous record. This is encapsulated in a "try/otherwise" expression to return a 1 if there's no previous record.

    Colin's version is similar and readable, use it.

    Thursday, November 8, 2018 9:16 PM
  • Hi Daniel,

    Yes, you can write a script with a single line of code - you don't even need a let..in block. However, not only would complex code be totally unreadable, there would be no easy way to test or debug each nested portion. Breaking down a complex script into readable, testable and maintainable pieces (easier to modify later), is always the best approach.

    Thursday, November 8, 2018 10:51 PM
  • You can remove the #Replace Errors" step by modifying the #"Added Custom" step:

    #"Added Custom" = Table.AddColumn(#"Added Previous", "Diff. in %", each try if [Previous] = null then [mm]/[mm] else [mm]/[Previous] otherwise null, Percentage.Type),
    

    Hi Colin

    Of course… THANKS again

    Friday, November 9, 2018 11:14 AM
  • Hi Laurent

    Any problem with the above proposals? Work? Don't work? Solve your problem?

    Monday, November 12, 2018 3:05 PM
  • If you just want to compare to the previous date, then adding a helper column and doing a Merge operation would be the most efficient way to go:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzLSNzIwtFDSUVIISCzKLFYAsgwNlGJ10GX9UssVIvOLssEKDPUssSgJyc+uzAfLm+mZwOWNsVhgomeMRR7ZCgs9cywqEDYYKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date  " = _t, #" City " = _t, #" mm " = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date  ", type date}, {" City ", type text}, {" mm ", type number}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "PreviousDate", each Date.AddDays([#"Date  "], -1)),
        #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"PreviousDate", " City "},#"Added Custom",{"Date  ", " City "},"Added Custom",JoinKind.LeftOuter),
        #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {" mm "}, {" mm .1"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"PreviousDate"})
    in
        #"Removed Columns"

    the cool part is that all of this can be done via the UI.

    Tuesday, November 13, 2018 1:34 PM