# % Difference to earlier date | Power Query

• ### 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?

Laurent

Wednesday, November 7, 2018 7:27 PM

• 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}})),
(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}}),
#"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}})),
(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}}),