1

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

Example on a data profile

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
Reza Azimi
  • 11
  • 7

3 Answers3

1

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.

Wedge
  • 1,766
  • 1
  • 8
  • 14
0

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
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • 1
    can you please send a screenshot on where in the query editor? I have some trouble to find out where do perform this function – Reza Azimi Jul 06 '18 at 08:24
  • You can write custom code in the Advanced Editor under the Home tab in the Power Query Editor. – Alexis Olson Jul 06 '18 at 13:55
  • @RezaAzimi, I have same question: Where does one enter `Table.Profile(TableName)`? You can't just slap it in the Advanced Editor as it will interfere with existing code there. – ericOnline Apr 17 '20 at 20:35
0

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

Power BI Ribbon

Jerome Pillay
  • 23
  • 4