Replace with latest not zero value (Power BI)

Hi Xilalus ,

 

Paste this over the defult code in Advanced Editor to follow my steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnZ00TXXNTIwMlLSUTI30zM2UYrVgQhbIAkbmcOFLWHCBnAhQwMsYoZIYrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [currencyMonth = _t, exchRate = _t]),
    chgInitialTypes = Table.TransformColumnTypes(Source,{{"currencyMonth", type text}, {"exchRate", type number}}),
    extractCurrency = Table.AddColumn(chgInitialTypes, "currency", each Text.BeforeDelimiter([currencyMonth], "-"), type text),
    extractDate = Table.AddColumn(extractCurrency, "exchDate", each Text.AfterDelimiter([currencyMonth], "-"), type text),
    chgExchDateType = Table.TransformColumnTypes(extractDate,{{"exchDate", type date}}),
    groupCurrency = Table.Group(chgExchDateType, {"currency"}, {{"data", each _, type table [currencyMonth=nullable text, exchRate=nullable number, currency=text, exchDate=nullable date]}}),
    addMaxExchDate = Table.AddColumn(groupCurrency, "maxExchDate", each Table.Max(Table.SelectRows([data], each [exchRate] <> 0), "exchDate")[exchRate]),
    expandMaxExchDate = Table.ExpandTableColumn(addMaxExchDate, "data", {"currencyMonth", "exchRate", "exchDate"}, {"currencyMonth", "exchRate", "exchDate"})
in
    expandMaxExchDate

 

Summary:

1) Split out currency and date portions of 'CAD-8-2022' column into their own columns.

2) Convert the new '8-2022' column to date format.

3) Group table on new 'CAD' column, adding an 'All Rows' aggregator.

4) Create a custom column that grabs the exchange rate for the latest date where the rate isn't zero.

5) Expand all rows back out again

 

Output:

 

From here, it's up to you how you want to handle it. You could just create a new column with 'if rate = 0 then [yourNewRateColumn] etc. or do a conditional replace on the old column etc.

 

Pete