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 ta...
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))

     

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))