Forum Discussion
How do I create a table of descriptive stats from numerous data sources?
I can't seem to find an answer that works for me, so looking to you all for help.
In my PBI report I have numerous data sources (in the example file, each "example table" is a separate data source in the report) and I want to create a way to build a table or matrix visual that displays the distinct count of [record_id] and the descriptive stats of [value] for each data source.
I want to make a table that outputs the following based on the example file
| Table 1 | Table 2 | Table 3 | |
| Count of Records | 6 | 3 | 3 |
| Min | 1 | 1 | 1 |
| Max | 3 | 7 | 7 |
| Mean | 2 | 4 | 5 |
| Median | 2 | 4 | 5 |
| Mode | 2 | 7 | 7 |
| Standard Dev. | 0.82 | 2.03 | 1.95 |
Any help would be greatly appreciated!
Hi... to get this stats for each table, you can use this function in Power Query:
let
Source = Table.Profile(#"YourTableName")
in
SourceThis will create a table, where each column of your table will be represented by single row and information about Min, Max, StandardDeviation, Average, Count, NullCount and DistinctCount will be provided. On top of it, you can create some or your own measures, e.g. completness, etc.... but is missing Mean, Mode and Median
You can do it for all tables, add new column with table name and then append them to a final single table 😉
I hope this helps 🙂
4 Replies
ivana_tomekovaAdvocate II
Hi... to get this stats for each table, you can use this function in Power Query:
let
Source = Table.Profile(#"YourTableName")
in
SourceThis will create a table, where each column of your table will be represented by single row and information about Min, Max, StandardDeviation, Average, Count, NullCount and DistinctCount will be provided. On top of it, you can create some or your own measures, e.g. completness, etc.... but is missing Mean, Mode and Median
You can do it for all tables, add new column with table name and then append them to a final single table 😉
I hope this helps 🙂- lbendlin
Super User
Or the DAX equivalent COLUMNSTATISTICS()
v-lgarikapatCommunity Support
Hi MeggieD00dles ,
Thanks for reaching out to the Microsoft fabric community forum.
lbendlin , ivana_tomekova Thanks for the prompt response
I wanted to follow up and confirm whether you’ve had the opportunity to review the information provided by lbendlin , ivana_tomekova If you have any questions or need further clarification, please don’t hesitate to reach out.
We appreciate your collaboration and support!
Best regards,
Lakshmi.- v-lgarikapat
Hi MeggieD00dles ,
We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.
We appreciate your engagement and thank you for being an active part of the community.
Best Regards,
Lakshmi.
