Replace values considering the Headers of the columns and the values of the rows (in another column)

[Patrickalvesjr]

Hi Guys, 

I have columns called "202301" till "202612" and i want to change the null values to 0 from this columns, but only if the first 4 letters ( the year ) matchs with the value of the Year column ( another column ), example:

If i have in the column "202301" a null value in the row 123 and in the same row in the column Year the value is "2023" the null value shoud be 0.

Someone can support me in this?

Thanks in advance.

[wdx223_Daniel]

NewStep=Table.FromRecords(Table.TransformRows(PreviousStepName,each Record.FromTable(Table.ReplaceValue(Record.ToTable(_),each [Name],[Year],(x,y,z)=>if Text.Start(y,4)=Text.From(z) then x??0,{"Value}))))

[Patrickalvesjr]

Excuse-me Daniel, I'm a beginner.

Thanks for your response, but i don't have sucess, look the code below:

= let
    // Step 1: Define the column mapping
    columnMapping = Table.FromRecords({
        [OldColumnName = "[Time].[Year].[Year].[MEMBER_CAPTION]", NewColumnName = "Year"],
        [OldColumnName = "[Time].[Y-M].[Month].&[202101]", NewColumnName = "202101"],
        [OldColumnName = "[Time].[Y-M].[Month].&[202102]", NewColumnName = "202102"],
        //many columns here
        [OldColumnName = "[Time].[Y-M].[Month].&[202610]", NewColumnName = "202610"],
        [OldColumnName = "[Time].[Y-M].[Month].&[202611]", NewColumnName = "202611"],
        [OldColumnName = "[Time].[Y-M].[Month].&[202612]", NewColumnName = "202612"]
    }),
    // Step 2: Filter the mappings for columns that exist in the original table
    existingColumnNames = Table.ColumnNames(Query1),
    filteredColumnMapping = Table.SelectRows(columnMapping, each List.Contains(existingColumnNames, [OldColumnName])),

    // Step 3: Rename the columns based on the filtered mapping
    transformedTable = Table.RenameColumns(Query1, Table.ToRows(filteredColumnMapping)),

    // Step 4: Replace null values in specific columns
    replacedNullWithZero = Table.TransformColumns(transformedTable, (columnName) =>
        let
            columnYear = Text.Start(columnName, 4),
            newColumnName = filteredColumnMapping[NewColumnName]{0}
        in
            if newColumnName <> null and [Year] = columnYear then
                Table.ReplaceValue(columnName, null, 0, Replacer.ReplaceValue)
            else
                columnName
    )
in
    replacedNullWithZero

Where is my error?

The system tells me that "Expression.Error: We cannot convert a value of type Function to type List."
Details:
Value=[Function]
Type=[Type]

Thanks a lot.

[wdx223_Daniel · Accepted solution]

the function of Table.TransformColumns can not get the column name and the value from other columns.

= let
    // Step 1: Define the column mapping
    columnMapping = Table.FromRecords({
        [OldColumnName = "[Time].[Year].[Year].[MEMBER_CAPTION]", NewColumnName = "Year"],
        [OldColumnName = "[Time].[Y-M].[Month].&[202101]", NewColumnName = "202101"],
        [OldColumnName = "[Time].[Y-M].[Month].&[202102]", NewColumnName = "202102"],
        //many columns here
        [OldColumnName = "[Time].[Y-M].[Month].&[202610]", NewColumnName = "202610"],
        [OldColumnName = "[Time].[Y-M].[Month].&[202611]", NewColumnName = "202611"],
        [OldColumnName = "[Time].[Y-M].[Month].&[202612]", NewColumnName = "202612"]
    }),
    // Step 2: Filter the mappings for columns that exist in the original table
    existingColumnNames = Table.ColumnNames(Query1),
    filteredColumnMapping = Table.SelectRows(columnMapping, each List.Contains(existingColumnNames, [OldColumnName])),

    // Step 3: Rename the columns based on the filtered mapping
    transformedTable = Table.RenameColumns(Query1, Table.ToRows(filteredColumnMapping)),

    // Step 4: Replace null values in specific columns
    replacedNullWithZero = Table.FromRecord(Table.TransformRows(transformedTable,
        each Record.FromTable(Table.ReplaceValue(Record.ToTable(_),each [Name],[Year],
            (x,y,z)=>if Text.Start(y,4)=Text.From(z) then x??0 else x,
             {"Value"})))
in
    replacedNullWithZero

[Patrickalvesjr]

It works!! Thanks Daniel.