Replace with latest not zero value (Power BI)

[Xilalus]

Hey guys,
I have a table from a data source, which contains exchange rates from EUR to whatevery other currency.

Obviously this table only contains exchange rates for months that have already finished.

But I want to use the latest exchange rate for a currency for future months as well, as long as I don't get one from the data source.

The data source already delivers many rows with 0 as exchange rate, so my idea would be to somehow copy the latest value for each currency into those rows.

Here an example how the table is structured:

I use the first column to join the exchange rates table with other tables if they need some currencies exchanged.

Thank you for your time 🙂

[BA_Pete · Accepted solution]

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

[Xilalus]

Thank you Pete, this is awesome. I was missing the group and max magic 🙂