Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
naveen73
Helper III
Helper III

Extraplation

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

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1707796347167.png

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.

 

 

View solution in original post

5 REPLIES 5
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1707796347167.png

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.

 

 

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:

naveen73_0-1708262565267.png

 

 

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

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
Super User
Super User

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

 

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

@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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors