Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
4 years ago
Solved

Trying to have multiple steps in one variable, getting error

Hi everyone,

I was trying to add another step to the result variable from this code here but kept running into the error: "The name 'result' wasn't recognized." I'd like to know how to properly correct the way I initialized the result variable if possible:

 

let
    Source = let
    CountryCleanup = (InputTable as table, ColumnName as text, optional StartRow as number) =>
    let
        ActualRow = if (StartRow = null) then 0 else StartRow,
        result =
            let
                CSCountryCleanup = Table.ReplaceValue(InputTable, CSCountry{ActualRow}[CS], CSCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName}),
                QTCountryCleanup = Table.ReplaceValue(CSCountryCleanup, QTCountry{ActualRow}[QT], QTCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName})
            in result,
        NextRow = ActualRow + 1,
        OutputTable = if NextRow > (Table.RowCount(CSCountry) - 1)
            then result
            else @CountryCleanup(result, ColumnName, NextRow)
    in OutputTable
in CountryCleanup
in
    Source

 

The code basically tries to compare the InputTable's Country column with the CS[Country] and QT[Country] tables/columns to standardize the country names (eg: Czech Republic → Czechia, etc.). Since our dataset came from 2 different sources before merge, I am running two instances of this function (one to compare with the CS table and another to compare with the QT table) so I figured it would be better if I combined the replacements in one function.

  • Anonymous's avatar
    Anonymous
    4 years ago

    try to change this parte of you code from:

     

    result =
                let
                    CSCountryCleanup = Table.ReplaceValue(InputTable, CSCountry{ActualRow}[CS], CSCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName}),
                    QTCountryCleanup = Table.ReplaceValue(CSCountryCleanup, QTCountry{ActualRow}[QT], QTCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName})
                in result,

     

    to:

    result =
                let
                    CSCountryCleanup = Table.ReplaceValue(InputTable, CSCountry{ActualRow}[CS], CSCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName}),
                    QTCountryCleanup = Table.ReplaceValue(CSCountryCleanup, QTCountry{ActualRow}[QT], QTCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName})
                in QTCountryCleanup,

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    try to change this parte of you code from:

     

    result =
                let
                    CSCountryCleanup = Table.ReplaceValue(InputTable, CSCountry{ActualRow}[CS], CSCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName}),
                    QTCountryCleanup = Table.ReplaceValue(CSCountryCleanup, QTCountry{ActualRow}[QT], QTCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName})
                in result,

     

    to:

    result =
                let
                    CSCountryCleanup = Table.ReplaceValue(InputTable, CSCountry{ActualRow}[CS], CSCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName}),
                    QTCountryCleanup = Table.ReplaceValue(CSCountryCleanup, QTCountry{ActualRow}[QT], QTCountry{ActualRow}[Country], Replacer.ReplaceValue, {ColumnName})
                in QTCountryCleanup,