Extraplation

[naveen73]

Hi All,

I want to interpolate. I was thinking of doing this in Power Query.

• Every month I have end-of-day values: see column D and E
• I want to pass the date values from my Excel cells: H1-J1
• The extrapolation is simple: take the difference between two end of month values and divides this by the number of days between the end of month dates. Then multiple this by the difference between the last end of month and the date of which the extrapolation needs to be performed.
• When the date in the H1-J1 is after the date in column C the value should be zero.

I am not able to get the headers of the column in date format so not sure if this would work. Or maybe it is better in DAX?

Please see link to the spreadsheet:

https://docs.google.com/spreadsheets/d/1HpTgrvfwqjncsuCN9UbojMsRS5tWw0Hl/edit?usp=sharing&ouid=11029...

Thanks,

Naveen

[v-xinruzhu-msft · Accepted solution]

Hi @naveen73 

You can refer to the following sample.

1.Create a query1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZJLDgQhCETv4noSFAXxLJ2+/zWmFQWd9GxY8FJ8Cq4rICTAiBQ+geoTuD2hYrg/V6jQOiudSU/nHupgBLwY8wpTlxKkaBSNRqU/HbVqGwwFxFoW1TjMUM6qslUVSGg06zAuZZcS2ZoKeVslWk9RIUE+ezaHT09XirWUP6sMyKqsQAdkUieGsjkcm/CwKQ0om7XkA/E8WDXWjOnBHnfaizCvNeuL76TK5h5QsmFplfWjFCtb5pO469mEdZ2ED1iLC/dzyc//5Lh5J2bPPDRub6AesJdNxX+PHco0D8+/7BZIXP60cxPZLNjmQU3rPPcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"29-Feb-24" = _t, #"31-Mar-24" = _t, #"30-Apr-24" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"29-Feb-24", Int64.Type}, {"31-Mar-24", Int64.Type}, {"30-Apr-24", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "29-Feb-24", "31-Mar-24", "30-Apr-24"})
in
    #"Reordered Columns"

Then create a blank query query2 and set it as  a function.

(Parameter1 as text)=>
let
    a=Date.FromText(Parameter1,"en-GB"),
    b=Table.AddColumn(Table.AddColumn(
    Table.AddIndexColumn(
    Table.FromList(
List.Skip(
Table.ColumnNames(Query1),2)),"Index",0,1),"Date",each Date.FromText([Column1],"en-GB")),"EndDate",each Date.EndOfMonth([Date])),
  c=Date.EndOfMonth(Date.AddMonths(a,-1)),
  d=Date.EndOfMonth(Date.AddMonths(a,-2)),
  e=Table.SelectRows(b,each ([EndDate]=c)),
  f=Table.SelectRows(b,each ([EndDate]=d)),
  g=Table.RenameColumns(
  Table.SelectColumns(Query1,{"Date","Index",b[Column1]{e[Index]{0}},b[Column1]{f[Index]{0}}}),{{b[Column1]{e[Index]{0}},"Date1"},{b[Column1]{f[Index]{0}},"Date2"}}) ,
h=Table.AddColumn(g,Parameter1,each if [Date]>=a then [Date1]+([Date1]-[Date2])/(Duration.Days(b[Date]{e[Index]{0}}-b[Date]{f[Index]{0}}))*(Duration.Days(a-b[Date]{e[Index]{0}})) else 0),
J=Table.SelectColumns(h,{"Index",Parameter1})
in J

 Then input the paramater "5/4/2024,6/4/2024,7/4/2024" it will generate invoke table1, table2,table3.

Then create a blank query query3 and input the folloing code

let
    Source = Query1,
    #"Merged Queries" = Table.NestedJoin(Source, {"Index"}, #"Invoked Function", {"Index"}, "Invoked Function", JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"Index"}, #"Invoked Function (2)", {"Index"}, "Invoked Function (2)", JoinKind.LeftOuter),
    #"Merged Queries2" = Table.NestedJoin(#"Merged Queries1", {"Index"}, #"Invoked Function (3)", {"Index"}, "Invoked Function (3)", JoinKind.LeftOuter),
    #"Expanded Invoked Function (2)" = Table.ExpandTableColumn(#"Merged Queries2", "Invoked Function (2)", {"6/4/2024"}, {"6/4/2024"}),
    #"Expanded Invoked Function" = Table.ExpandTableColumn(#"Expanded Invoked Function (2)", "Invoked Function", {"5/4/2024"}, {"5/4/2024"}),
    #"Expanded Invoked Function (3)" = Table.ExpandTableColumn(#"Expanded Invoked Function", "Invoked Function (3)", {"7/4/2024"}, {"7/4/2024"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Invoked Function (3)",{"Index"})
in
    #"Removed Columns"

 Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[naveen73]

Hi,

Thanks for this solution. This is helpfull.

Hoewever, I need to add that the data comes from MS Database (and in future from an SQL database)

A screenshot of the table in MS Access is as below:

Will it be possible to change the code so that it takes into account.

• Monthly update that adds new data to the table for each new month?
• The user will give input from the Excel which date shall be used to be retrieved in the Excel sheet after data processing in Power Query engine. This can be cells H1-J1.

I have attached both the MS Access file and the Excel file.
https://docs.google.com/spreadsheets/d/1HpTgrvfwqjncsuCN9UbojMsRS5tWw0Hl/edit?usp=sharing&ouid=11029...

https://drive.google.com/file/d/1-IHPBZpk_lauermdYjSXUlUsyU5m-mF4/view?usp=sharing

thanks,

Naveen

[v-xinruzhu-msft]

Hi @naveen73 

The solution I provided baed on the sample data you have provided at first, 

What you're asking now is a new requirement, if my original proposal helps, please consider marking it as a solution, and then consider starting a new post for your new requirement.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Once you have that data, what are you planning to do with it?  

Note that this year last day of February is the 29th.

[naveen73]

@lbendlin 

1. I will sum up per month and present in a table. I do not want to do a “groupby” in the PQ engine as I still want to show the data on a Product level
2. At some point I want to show the sum of the values in a graph