Forum Discussion

pucities's avatar
pucities
Frequent Visitor
6 years ago
Solved

Data Profiling in M: Count of Numeric Values (Table.Profile)

Hello,

 

I created a data profiling table in Power Query from a table(T_File)

 

let
Source = Table.Profile(T_File)
in
Source

 

 

How do I add another aggregate column to the data profile table that shows the count of numeric values?

 

I found an arcticle which shows how to add aggregate columns 

https://blog.crossjoin.co.uk/2019/07/31/adding-more-aggregate-columns-to-the-output-of-table-profile/

   Table.Profile(
        Source,
        {
        {"Median", each Type.Is(_, type number), List.Median},
        {"Mode", each Type.Is(_, type number), List.Mode}
        }
    )
 

 

But I can't seem to figure out how to count numeric values from varchar formatted field.

 

Thank you

 

 

 

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi pucities 

    Please try this:

    List.Count(List.Select( Source[Column1], each try Value.Is(Number.From(_), type number) otherwise null))

     

4 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi pucities 

    not sure if my understanding is correct, but you could use the function List.Count to count the occurrances instead:

     

       Table.Profile(
            Source,
            {
            {"Count", each Type.Is(_, type number), List.Count},
            {"Mode", each Type.Is(_, type number), List.Mode}
            }
        )

     

    • pucities's avatar
      pucities
      Frequent Visitor
      6 years ago

      Thanks for your reply. 

       

      I should have been more specific in my question. 

       

      The column type is varchar(Text) so I get "null"  when I use this function:    {"Count", each Type.Is(_, type number), List.Count},

       

      Currently, the column that I'm interested in contains both numeric and alphanumeric values. But I want the count of only numeric records.

       

      For example, from the following list, I want to return 1.

      12356

      13BA3

      352B1

       

      Thank you

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Hi pucities 

        Please try this:

        List.Count(List.Select( Source[Column1], each try Value.Is(Number.From(_), type number) otherwise null))

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi pucities 

    Before Profile, please change type of your column from varchar to number format.

    Please pay attention to the notes below:

    The second part: A function to call to determine where the aggregate column will be applied to a given column in the source table. In this case I’m checking to see if a column is type number and only returning a median or mode if it is.

     

    Reference:

    https://ssbi-blog.de/blog/technical-topics-english/pitfalls-with-table-columnsoftype/

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.