Fill "null" automaticaly with last year value

[Anonymous]

Hello,

I have the following problem: For some Projects(ProjectID) and some years I do not have exchange rates (FX). 

I need a algorithm which fills the null-cells automatically. It needs to consider following conditions:

1. If there is no value for the year before for the specific ProjectID use the FX-Rate of the following year

2. If there is no value for the year after for the specific ProjectID use the FX Rate of the year before

I really appreciate your support.

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBbCsAgDETRveRbikmMj7WI+9+G0lBqp34NHAIX0juJMTMFkihxDY3wMT6YrOFLkNXZ0NPtimzOv/N8KJbHVLW1N4e2pza2M2dnQS/uCb26Z/TmXsHV/zkm", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, Year = _t, #"FX-Rate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", Int64.Type}, {"Year", Int64.Type}, {"FX-Rate", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectID"}, {{"Temp", each _, type table [ProjectID=nullable number, Year=nullable number, #"FX-Rate"=nullable number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"ProjectID"}),
    //Function Start
    fxProcess=(Tbl)=>
        let
            #"Filled Down" = Table.FillDown(Tbl,{"FX-Rate"}),
            #"Filled Up" = Table.FillUp(#"Filled Down",{"FX-Rate"})
        in
    #"Filled Up",
    //Function End
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each fxProcess([Temp])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Temp"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"ProjectID", "Year", "FX-Rate"}, {"ProjectID", "Year", "FX-Rate"})
in
    #"Expanded Custom"

[Anonymous]

Hello, thanks for the extensive feedback. Looks quiet complicated. Unfortunately I do not understand where I have to insert this code. Maybe you can give me a feedback. 

Thank you very mich in advance!

[Vijay_A_Verma]

Right click on your table in Power BI - Edit Query (to invoke Power Query)

When you are in PQ, Home menu - Advanced Editor - Delete everything after Source line.

From my code copy all starting with #"Changed Type" till end.

Paste into Advanced Editor after Source line (Make sure Source line has a comma at the end)

OK

Close and Apply