Forum Discussion

Newbie22's avatar
Newbie22
Resolver I
3 years ago
Solved

Replace Value of multiple columns using Query Editor but the columns are increasing every refresh.

Hi All,

 

I have a growing file as data source. Columns are increasing every refresh in Power BI.

I need to replace all columns with "null" to 0 and it should also replace those "additional" columns aside from the existing columns.

 

I don't want to go inside Query Editor every time there's added column and then replace the value.

 

 

Is there any M code that I can use to have every columns change automatically? 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • ams1's avatar
    ams1
    3 years ago

    Hi,

     

    The code in your Advanced Editor should look something like below.

     

    let
Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Downloads\sampledata.xlsx"), null, true),
#"Removed Other Columns1" = Table.SelectColumns(Source,{"Data"}),
Data = #"Removed Other Columns1"{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data, [PromoteAllScalars=true]),
// **********
ReplaceNulls = Table.TransformColumns(#"Promoted Headers",{},(x) => Replacer.ReplaceValue(x,null,0))
// **********
in
ReplaceNulls // don't forget about this also 🙂

     

4 Replies

  • ams1's avatar
    ams1
    Responsive Resident
    3 years ago

    Hi,

     

    IF your whole InputTable looks something like this (?):

    AND you get nulls only in "values",

    THEN you could replace all nulls with:

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

     

    Not sure what you mean ref replacing those "additional" columns, but maybe:

    1) UNPIVOT (after replacing nulls)

    2) Filter out/keep only the columns you want (?)

    3) Pivot back

     

    • Newbie22's avatar
      Newbie22
      Resolver I
      3 years ago

      Hello ams1 

       

      Thanks for your quick reply.

       

      Where do I put this code? 

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

       

      How do i add that in my code?

      Below is my code inside Advanced Editor: 

       

      let
      Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Downloads\sampledata.xlsx"), null, true),
      #"Removed Other Columns1" = Table.SelectColumns(Source,{"Data"}),
      Data = #"Removed Other Columns1"{0}[Data],
      #"Promoted Headers" = Table.PromoteHeaders(Data, [PromoteAllScalars=true])
      in
      #"Promoted Headers"

      • ams1's avatar
        ams1
        Responsive Resident
        3 years ago

        Hi,

         

        The code in your Advanced Editor should look something like below.

         

        let
Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Downloads\sampledata.xlsx"), null, true),
#"Removed Other Columns1" = Table.SelectColumns(Source,{"Data"}),
Data = #"Removed Other Columns1"{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data, [PromoteAllScalars=true]),
// **********
ReplaceNulls = Table.TransformColumns(#"Promoted Headers",{},(x) => Replacer.ReplaceValue(x,null,0))
// **********
in
ReplaceNulls // don't forget about this also 🙂