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

[Newbie22]

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? 

[ams1]

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]

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 · Accepted solution]

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]

Thank you so much @ams1 !! It worked perfectly. You're the best 🤗