Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pucities
Frequent Visitor

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

 

 

 

1 ACCEPTED SOLUTION

Hi @pucities 

Please try this:

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @pucities 

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

Capture12.JPG

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.

ImkeF
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Hi @pucities 

Please try this:

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors