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" colum...
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 🙂

     

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 🙂

     

    • Newbie22's avatar
      Newbie22
      Resolver I
      3 years ago

      Thank you so much ams1 !! It worked perfectly. You're the best ğŸ¤—