Data profiling in Power BI

Question

I want to profile every single data table I have in my Power BI report. By data profile I mean something like this:

Are there ways to make a data profile view in Power BI? DAX measure or calculated columns?

Alternatively, you can also recommend other data quality tools that can handle such tasks since I find it a bit difficult to achieve this result in Power BI.

Answer 1

In the query editor, you can use the Table.Profile function on any table.

You can do multiple ones simultaneously like this:

= Table.Combine({Table.Profile(Table1),Table.Profile(Table2)})

---

Edit:

To see the profile, create a new Blank Query and define it as = Table.Profile(Table1). If you open the Advanced Editor, the M code looks like this:

let
    Source = Table.Profile(Table1)
in
    Source

Answer 2

Now I feel dumb after writing a manual query that did what it turns out Table.Profile does in one shot. However I will mention you can automatically get a profile for every table in your data set by using the #shared reference and filtering down to the tables:

let
    Source = #shared,
    #"Converted to Table" = Record.ToTable(Source),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "TableCheck", each Type.Is(Value.Type([Value]), type table)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([TableCheck] = true)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Name] <> "NAME_OF_THIS_QUERY"),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "Profile", each Table.Profile([Value])),
    #"Expanded Profile" = Table.ExpandTableColumn(#"Added Custom1", "Profile", {"Column", "Min", "Max", "Average", "StandardDeviation", "Count", "NullCount", "DistinctCount"}, {"Profile.Column", "Profile.Min", "Profile.Max", "Profile.Average", "Profile.StandardDeviation", "Profile.Count", "Profile.NullCount", "Profile.DistinctCount"})
in
    #"Expanded Profile"

And replace "NAME_OF_THIS_QUERY" with whatever you name the query so it doesn't try to profile itself.

Answer 3

Power BI has a built-in data profiler

1. Open Power BI and refer to the menu ribbon
2. Click Home
3. Click Edit Queries
4. Click View
5. Select Column Profile to view stats about your data

---