Replace blanks in an unknown number of columns with 0

[Ashish_Mathur]

Hi,

I have a dataset with 4 text fields and 3 numeric fields - Oct 2019, Nov 2019 and Dec 2019.  The entries in the last 3 columns will either contain numbers or remain blank.  In the Query Editor, it is easy to replace blanks with 0's.  However once i create a column for Jan 2020, on refreshing, i would like the blank cells in that column to also get populated with 0's.  Likeweise as and when i keep add columns for months, on refresing, i would like to the blank cells to get populated with 0's

How can this be done in the Query editor?

Thank you for your help.

[Ashish_Mathur · Accepted solution]

Hi @AnkitBI,

You shared this M code but then why did you delete it?

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSgWMjKFaK1cGUAFPoEnBNIAksJmHqIEcGiqipIRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [First = _t, Second = _t, #"Third " = _t, #"Oct-19" = _t, #"Nov-19" = _t, #"Dec-19" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"First", type text}, {"Second", type text}, {"Third ", type text}, {"Oct-19", Int64.Type}, {"Nov-19", Int64.Type}, {"Dec-19", Int64.Type}}), GetColumnNames = List.Skip(Table.ColumnNames(#"Changed Type"),3), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,GetColumnNames) in #"Replaced Value"

[Ashish_Mathur · Accepted solution]

Hi @AnkitBI,

You shared this M code but then why did you delete it?

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSgWMjKFaK1cGUAFPoEnBNIAksJmHqIEcGiqipIRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [First = _t, Second = _t, #"Third " = _t, #"Oct-19" = _t, #"Nov-19" = _t, #"Dec-19" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"First", type text}, {"Second", type text}, {"Third ", type text}, {"Oct-19", Int64.Type}, {"Nov-19", Int64.Type}, {"Dec-19", Int64.Type}}), GetColumnNames = List.Skip(Table.ColumnNames(#"Changed Type"),3), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,GetColumnNames) in #"Replaced Value"

[AnkitBI · Accepted solution]

Hi @Ashish_Mathur 

Below Code works perfectly fine and will fulfil your requirement. 

 GetColumnNames = List.Skip(Table.ColumnNames(#"Changed Type"),3), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,GetColumnNames) in #"Replaced Value"

Actually Plan was to Edit the #"Changed Type" to make it also dynamic for all the integer columns. However then I had to leave and couldn't work on it. Let me know if above solves your problem. I will anyways work on the #"Changed Type"  logic for my knowledge.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[Ashish_Mathur]

Thank you very much.  That worked very well.