Forum Discussion
How to interpolate between monthly data (Excel Power Query)
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 th...
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"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).
Please show the expected outcome. Not clear to me from your description.
