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 want to replace Null value for all columns with zero in power BI Query.
For example,
Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"X1 ", "X2", "X3"})
I do not want to write my column name like {"X1 ", "X2", "X3"}.
Is there any function in M for replacing all columns with one value ?
Thanks
Nicki
Solved! Go to Solution.
Hi @Nicki,
you can use Table.TransformColumns function for your expected output.
let
// my test data
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiI6VYnWgQwxjEj40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
// get all column names as list
allColumnNames = Table.ColumnNames(Source),
// transform the column name list into a list of list, where every inner list contains column name and a function for replacing null value
allTranformations = List.Transform(allColumnNames, each {_, each if _ = null or _ = "" then 0 else _}),
// apply the transformations
tranformColumns = Table.TransformColumns(Source, allTranformations)
in
tranformColumns
Mas e se para cada campo da tabela fosse uma substituição diferente, como ficaria o código?
Hi @Nicki ,
We can ctrl + click to chooes multi columns to work around.
Hi @Nicki,
you can use Table.TransformColumns function for your expected output.
let
// my test data
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiI6VYnWgQwxjEj40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
// get all column names as list
allColumnNames = Table.ColumnNames(Source),
// transform the column name list into a list of list, where every inner list contains column name and a function for replacing null value
allTranformations = List.Transform(allColumnNames, each {_, each if _ = null or _ = "" then 0 else _}),
// apply the transformations
tranformColumns = Table.TransformColumns(Source, allTranformations)
in
tranformColumns
Thank you. It works fine.
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.