none
How to refer to a column by its position when replace null to zero RRS feed

  • General discussion

  • Hello,

    I just need to apply step of replace several columns values from "null" to 0 (zero). If I go in using the menu Transform> Replace Values it will generate this code, choosing 2 columns:

    Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"2018_06", "2018_07"})

    Well that works well however my columns names change each week. So I need to change this code to refer to the columns by its position I believe... something like this:

    = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{Table.ColumnNames(#"Replaced Value"){13}, Table.ColumnNames(#"Replaced Value"){14}})

    But that is not working and I'm newcomer to M language. Please someone help me I tried a lot of thing over internet and nothing seems to work.

    Monday, February 19, 2018 6:09 PM

All replies

  • Excel 2016 Power Query
    Refer to columns by position.
    Advanced "M".
    http://www.mediafire.com/file/vuryv5rrjwc7c13/02_19_18a.xlsx

    Monday, February 19, 2018 9:23 PM
  • = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{Table.ColumnNames(#"Replaced Value"){13}, Table.ColumnNames(#"Replaced Value"){14}})


    There is an error in your code - you have to refer to the proper table when aquiring column names:

    = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{Table.ColumnNames(#"Removed Columns"){13}, Table.ColumnNames(#"Removed Columns"){14}})


    Maxim Zelensky Excel Inside

    Tuesday, February 20, 2018 7:24 AM