Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
But I can't seem to figure out how to count numeric values from varchar formatted field.
Thank you
Solved! Go to 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
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.
Hi @pucities
not sure if my understanding is correct, but you could use the function List.Count to count the occurrances instead:
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