Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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.
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"
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.
Thank you very much. That worked very well.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |