Substitute negative values on columns based on their position

Hi,

Without seeing some sample data, I can't be sure this is the best solution, but:

GIVEN the following input:

WHEN:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // Your table should be here
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "NewValue", each if [Value] < 0 then 0 else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "NewValue"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

THEN you will get the values replaced:

Using UNPIVOT, you move the columns to rows so that column names don't matter anymore.

This could also be addapted so that you can remove certain columns from replacement etc.

It might be like shooting an ant with a cannon, but there are a lot of unknowns and you should definitely know/read about UNPIVOT 😊 #unpivotAnything.

Please mark this as answer if it helped.

#"New Column" = Table.AddColumn(#"02/2021 (ajustado)", "New Column", each let a=Record.Field(_,Record.FieldNames(_){13}) in  if a < 0 then 0 else a),