How to interpolate between monthly data (Excel Pow...

JollyRoger01 · ‎07-22-2021

I have some historical data (approximtely 1 month apart, but newer historical may be weekly) and someone has helped to interpolate the data so we have it daily chunks, however it is stepped rather than a linear line between the data periods. I'd like to linearly interpolate between these reporting periods. Would someone please be able to alter the Power Query for me to show this?

Interpolation

lbendlin · ‎07-23-2021

Here is a very simple and dirty trick that relies on some peculiar behavior of deduplication:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdLBbeNAAEPRXnwOEJOjGUm1BOm/jSw2AmTE7/pvD+TX1yP9zGeffT4+HrPH4/vjfyvaQNvQJtpC29EOtBMtT0VJIkpkiTCRJuJEnggUiSpRuY1ElagSVaJKVIkqUSUaEo130e8B8y9lzl6taANtQ5toC21HO9BOtN8D/o2SRJTIEmEiTcSJPBEoElWichuJKlElqkSVqBJVoko0JBrvot4P7NzX1Yo20Da0ibbQdrQD7US7H/gaJYkokSXCRJqIE3kiUCSqROU2ElWiSlSJKlEl6rto3Bca6/m8WtEG2oY20RbajnagnWj3hV6jJBElskSYSBNxIk8EikSVqNxGokpUiSpRJapElagSDYnGu2i7H7itblcr2kDb0CbaQtvRDrQT7X7ga5QkokSWCBNpIk7kiUCRqBKV20hUiSpRJapElagSVaLxLpr32ebajqsVbaBtaBNtoe1oB9qJdp/tNUoSUSJLhIk0ESfyRKBIVInKbSSqRJWoElWiSlSJKtGQaLyL1v3AtfZerWgDbUObaAttRzvQTrT7ga9RkogSWSJMpIk4kScCRaJKVG4jUSWqRJWoElWiSlSJxh/R9w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row Labels" = _t, #"Sum of Changes" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row Labels", type date}, {"Sum of Changes", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Sum of Changes"})
in
    #"Removed Duplicates"

JollyRoger01 · ‎07-26-2021

Hi Ibendlin, thank you for your answer. However, it is a little different from what I am actually after. You'll see my data has unique entries for each date, which I need to keep, with the exception of the Changes Completed. That I would like to interpolate between the months (reporting periods).