CODEWITHSUNDEEP
powerbidata-profiling
Reza Azimi
Reza Azimi

Reputation: 11

Data profiling in Power BI

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.

Upvotes: 1

Views: 1789

Answers (3)

Alexis Olson
Alexis Olson

Reputation: 40204

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

Upvotes: 0

Jerome Pillay
Jerome Pillay

Reputation: 23

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

Upvotes: 0

Wedge
Wedge

Reputation: 1826

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.

Upvotes: 1

Related Questions