Substitute negative values on columns based on their position

[RodCamargoJr]

Hi guys,

I'm trying to substitute the negatives values of my table to 0 here on Power Query,

I have 24 fixed columns on my table that I need to make such adjustments,

But the problem is that these columns change their name every week,

Now I'm using a basic IF ELSE to adjust the values, but every time the columns change their name, I have to adjust the code,

This is one example of my current code, for one column, 

Example:
#"New Column" = Table.AddColumn(#"02/2021 (ajustado)", "New Column", each if [#"03/2021"] < 0 then 0 else [#"03/2021"]),

The column I'm adjusting is named 03/2021, but its position is {13},

How can I adjust the code to get the column by its position rather than its name?

[ams1 · Accepted solution]

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.

[wdx223_Daniel · Accepted solution]

#"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),

[ams1 · Accepted solution]

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.

[RodCamargoJr]

Thanks @ams1 , the feature of Pivot/Unpivot is really useful, it can work on multiples cenarios, and even, help you adjust multiples columns at once. Thanks again.