none
Power Query - Dynamic Column Selection? RRS feed

  • Question

  • Hi All,

    I'm trying to replace null values in columns to 0 but the number of columns will increase each month. Is there a way to dynamically select the columns on which to apply the transformation?

    Many thanks in advance


    • Edited by MAS42 Thursday, July 20, 2017 1:56 PM clarification
    Thursday, July 20, 2017 1:54 PM

Answers

  • From your question it is not clear if you want to replace nulls with 0 in ALL table columns.

    If so, you can use the following code. In this example, Table3 is the source table:

    let
        Source = Table3,
        ReplaceNulls = Table.TransformColumns(Source,{},(x) => Replacer.ReplaceValue(x,null,0))
    in
        ReplaceNulls

    • Proposed as answer by Chris Dutch Thursday, July 20, 2017 9:24 PM
    • Marked as answer by MAS42 Friday, July 21, 2017 9:07 AM
    Thursday, July 20, 2017 2:19 PM

All replies

  • From your question it is not clear if you want to replace nulls with 0 in ALL table columns.

    If so, you can use the following code. In this example, Table3 is the source table:

    let
        Source = Table3,
        ReplaceNulls = Table.TransformColumns(Source,{},(x) => Replacer.ReplaceValue(x,null,0))
    in
        ReplaceNulls

    • Proposed as answer by Chris Dutch Thursday, July 20, 2017 9:24 PM
    • Marked as answer by MAS42 Friday, July 21, 2017 9:07 AM
    Thursday, July 20, 2017 2:19 PM
  • Hi Marcel,  

    As my initial question was slightly unclear it's not exactly what I thought I wanted but it's an alternative approach I was consider as well and it does the job I needed perfectly so many thanks. 

    Out of interest & to clarify my original question, my table has several columns of text data that I don't want/need to replace values on followed by columns containing monthly values with some 'null' values. At the start of the FY only April data appears, followed each month by May, Jun etc.  Hence my original thought of selecting columns dynamically then replacing nulls for 0.  Because the 1st 4 text columns all have data in then your solution works just fine. 

    So, to rephrase my original question, it is possible to dynamically select columns in a table to replace values whilst omitting certain other columns? 

    In a sense I suppose what I was looking for was, just as you can chose to 'unpivot other columns' (and hence in a way are dynamically selecting any new columns that appear), something like 'select other columns' and then replace null with 0.


    • Edited by MAS42 Friday, July 21, 2017 9:23 AM clarification
    Friday, July 21, 2017 9:18 AM
  • It can be done in the advanced editor.

    You can either skip a fixed number of columns, or skip all columns until the first column you want to adjust.

    Both alternatives in the code below:

    let
        Source = Table1,
    
        // First alternative: skip a fixed number of columns:
        Columns1 = List.Skip(Table.ColumnNames(Source),4),
        #"Replaced Value1" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Columns1),
    
        // Second alternative: skip all columns until the first column that must be adjusted:
        Columns2 = List.Skip(Table.ColumnNames(Source), each _ <> "Column5"),
        #"Replaced Value2" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Columns2)
    in
        #"Replaced Value2"

    Friday, July 21, 2017 12:08 PM
  • Thanks - I'll give this a go.
    Friday, July 21, 2017 12:59 PM
  • It can be done in the advanced editor.

    You can either skip a fixed number of columns, or skip all columns until the first column you want to adjust.

    Both alternatives in the code below:

    let
        Source = Table1,
    
        // First alternative: skip a fixed number of columns:
        Columns1 = List.Skip(Table.ColumnNames(Source),4),
        #"Replaced Value1" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Columns1),
    
        // Second alternative: skip all columns until the first column that must be adjusted:
        Columns2 = List.Skip(Table.ColumnNames(Source), each _ <> "Column5"),
        #"Replaced Value2" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Columns2)
    in
        #"Replaced Value2"

    Hi Marcel,

    Please, but if a have, in that second alternative, more than one column to skip? Like, <> "Column 5" and "Column 6" or n columns??

    Wednesday, July 17, 2019 5:49 PM