Forum Discussion
add countblank to table.profile
- 5 years ago
Try this Anonymous
I don't think you can have blanks for numerical fields - they get converted to nulls, and you cannot replace values with blanks, so I am assuming this is text.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYhOlWJ1opSQUXjKUB+akgBhQNoRpCGanQRUBebEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data 1" = _t, #"Data 2" = _t, #"Data 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data 2", type number}, {"Data 3", type number}}), Custom1 = Table.Profile( #"Changed Type", { { "Blanks", each Type.Is(_, type nullable any), each List.Count(List.Select(_, each _ = "")) } } ) in Custom1This is my dummy data:
And this is the result. Note: I removed a bunch of columns Table.Profile generates to get this screenshot:
Try this Anonymous
I don't think you can have blanks for numerical fields - they get converted to nulls, and you cannot replace values with blanks, so I am assuming this is text.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYhOlWJ1opSQUXjKUB+akgBhQNoRpCGanQRUBebEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data 1" = _t, #"Data 2" = _t, #"Data 3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data 2", type number}, {"Data 3", type number}}),
Custom1 =
Table.Profile(
#"Changed Type",
{
{
"Blanks", each Type.Is(_, type nullable any), each List.Count(List.Select(_, each _ = ""))
}
}
)
in
Custom1
This is my dummy data:
And this is the result. Note: I removed a bunch of columns Table.Profile generates to get this screenshot:
Anonymous - did you try this solution?
- Anonymous4 years agoNot applicable
and also if i ignore the changed type rows (both) i get an error message
Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=[List]
Type=[Type]