Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

add countblank to table.profile

Hi,

 

I use table.profile to get the data profile of a large dataset with many columns. I believe NullCount does not count Blank cells. How can i add CountBlank to this profile? Thanks.

 

Need Help
  • edhans's avatar
    edhans
    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
    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:

     

     

     

11 Replies

  • edhans's avatar
    edhans
    Community Champion
    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
    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:

     

     

     

    • edhans's avatar
      edhans
      Community Champion
      5 years ago

      Anonymous - did you try this solution?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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]

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Edhans

       

      thanks for the solution but I don't understand the part : 

      Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYhOlWJ1opSQUXjKUB+akgBhQNoRpCGanQRUBebEA", BinaryEncoding.Base64), Compression.Deflate)),

       

      my table comes is imported from a data base, not a json file, so how can I adapt this please,

       

      cordially

      walid

    • edhans's avatar
      edhans
      Community Champion
      5 years ago

      Great Anonymous - glad I was able to help. I learned something new here too!