To know about data validation techniques

[Kmanikanta]

Hi all, I am working on a power bi project where I need to develop multiple reports that has visuals like table, slicers kpis. WI am validating the data manually in the reports as of now like how many records are there in a table and among those how many were duplicated and I am keeping a summary about these all. Now I want to do these all automatically and  at the end it will generate a report saying that total records are this much and these are the records that are duplicated for every option mentioned in a slicer. What are the techniques that are available to achieve this. Please give me the reply ASAP. 

[v-mengmli-msft]

Hi @Kmanikanta ,

I recommend that you use Power Automate to export report to PDF or create report subscription. For report subscription, you can create subscription directly in the Service after publishing report to the Service. Email subscriptions for reports and dashboards in the Power BI service - Power BI | Microsoft Learn

Licenses: Power Automate license, Power BI Pro or PPU

Here's my test of creating a flow export report for your reference.

Original data.

Step1. Create measures for statistics.

Count = CALCULATE(COUNTROWS('Table'),ALL('Table'),'Table'[ID]=MAX('Table'[ID]))

Total = COUNTROWS(ALL('Table'))

Step2. Save and Publish report to Service.

Step3. Create Flow.

Best regards,

Mengmeng Li

[danextian]

hI @Kmanikanta 

DAX has some useful functions which some can be used in a calculated table, some within the DAX query view.

For example COLUMNSTATISTICS() can return the list of columns in each table, the min and max values as well as the cardinality. INFO.VIEW functions can return calculated tables.

[grazitti_sapna]

Hi @Kmanikanta,

Automating data validation in Power BI involves leveraging DAX calculations, visuals, and possibly Power Query transformations to perform checks and generate a summary report. Here’s a structured approach to address your requirements:

1. Set Up the Data Validation Framework
Prepare the data for validation by identifying the key metrics you want to check, such as:

• Total records.
• Count of duplicate records.
• Summary statistics for slicer options.

2. Identify and Handle Duplicates
2.1. Add a Duplicate Flag
Create a calculated column in Power BI to flag duplicates using DAX:

• Duplicate Flag = 
  IF(
  COUNTROWS(
  FILTER(
  TableName,
  TableName[KeyColumn] = EARLIER(TableName[KeyColumn])
  )
  ) > 1,
  "Duplicate",
  "Unique"
  )

  Replace KeyColumn with the column or combination of columns that define uniqueness.

2.2. Count Duplicate Records
Create a measure to count duplicate records:

• Duplicate Count = 
  CALCULATE(
  COUNTROWS(TableName),
  TableName[Duplicate Flag] = "Duplicate"
  )

2.3. Count Total Records
Create a measure for the total record count:

• Total Records = COUNTROWS(TableName)

3. Validate Data by Slicer Options
If you have slicers that filter data, you can create measures to validate the records dynamically based on slicer selections.

Example: Count Records for a Selected Slicer Option

If your slicer is based on a column like Category, you can create a measure:

• Records by Slicer = 
  CALCULATE(
  COUNTROWS(TableName),
  ALLSELECTED(TableName[Category])
  )

Similarly, you can create a measure for duplicate counts per slicer:

• Duplicates by Slicer = 
  CALCULATE(
  COUNTROWS(TableName),
  TableName[Duplicate Flag] = "Duplicate",
  ALLSELECTED(TableName[Category])
  )

4. Generate Validation Report
Use the calculated measures to create a summary table or card visuals in the report:

• Total Records.
• Duplicate Count.
• Valid Records (Total - Duplicates).
• Dynamic counts based on slicer selections.

Table Visual Example
Add a table visual with columns:

• Slicer Column (e.g., Category).
• Total Records.
• Duplicate Count.
• Unique Records.

5. Optional: Automate Data Validation with Power Query
In Power Query, you can perform data validation and create a summary:

1. Group Data: Use the Group By feature to count duplicates and unique records.
2. Add Validation Columns: Add columns for record counts and flags for duplicates.
3. Load Summary Table: Create a separate summary table in Power Query to load into your report.

6. Enable Alerts for Validation Failures
Power BI allows conditional formatting. Use it to highlight anomalies:

• Set thresholds for duplicates or invalid data.
• Apply conditional formatting in visuals (e.g., red for high duplicates).

7. Advanced Automation with Power Automate
If you want to schedule or trigger the validation process:

• Use Power Automate to refresh datasets and email validation reports automatically.

Let me know if you need help implementing any specific step!

I hope the provided solution works for you

If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.

[Kmanikanta]

Thanks for the solution, Could you please eloberate on automation with power automation with example. 

[123abc]

Automate to automate data validation and report generation in Power BI:

Example: Automating Data Validation and Reporting

1. Set Up Your Data Source

Ensure your data is loaded into Power BI and is accessible. This could be from an Excel file, SQL database, or any other data source.

2. Create a Flow in Power Automate

1. Log in to Power Automate: Go to Power Automate and log in with your Microsoft account.
2. Create a New Flow: Click on “Create” and choose “Automated cloud flow”.
3. Select a Trigger: Choose a trigger that starts the flow. For example, “When a new item is created” in a SharePoint list or “When a new row is added” in an Excel file.

3. Add Actions to the Flow

1. Get Data: Use the “Get rows” action to fetch data from your data source.
2. Validate Data: Add conditions to check for duplicates or other validation rules. For example:
   • Check for Duplicates:

     Condition: If the count of rows with the same unique identifier is greater than 1, mark as duplicate.

3. Generate Summary: Use the “Compose” action to create a summary of the validation results.
4. Create a Report: Use the “Create HTML table” action to format the summary into a report.

4. Send the Report

1. Email the Report: Use the “Send an email” action to send the report to stakeholders.
2. Save the Report: Optionally, save the report to a SharePoint library or OneDrive.

Example Flow

Here’s a simplified example of what the flow might look like:

1. Trigger: When a new row is added in Excel.
2. Action: Get rows from the Excel file.
3. Action: Apply to each row:
   • Condition: Check for duplicates.
   • Action: If duplicate, add to a summary.
4. Action: Create an HTML table with the summary.
5. Action: Send an email with the HTML table.

Benefits

• Automation: Reduces manual effort and errors.
• Consistency: Ensures data validation is performed consistently.
• Efficiency: Saves time by automating repetitive tasks.

By following these steps, you can automate your data validation process and generate reports automatically using Power Automate. If you need more specific details or have any questions, feel free to ask!

[123abc]

How to Use Data Validation in Power BI Dashboards for Accurate Insights:

Please follow below link, here is a brief detail how to use validation in Power BI.

How to Use Data Validation in Power BI Dashboards for Accurate Insights | Poespas Blog

OR read below assumed solution:

Data Validation with DAX Measures

You can create DAX measures to validate your data. For example, you can create measures to count total records, identify duplicates, and summarize these findings. Here’s a basic example:

TotalRecords = COUNTROWS('YourTable')

DuplicateRecords = 
CALCULATE(
    COUNTROWS('YourTable'),
    FILTER(
        'YourTable',
        COUNTROWS(
            FILTER(
                'YourTable',
                'YourTable'[ID] = EARLIER('YourTable'[ID])
            )
        ) > 1
    )
)

2. Using Power Query for Data Validation

Power Query can be used to clean and validate data before it is loaded into Power BI. You can create custom queries to check for duplicates, missing values, and other data quality issues.

3. Data Profiling in Power Query

Power Query has built-in data profiling tools that can help you understand the quality of your data. You can enable data profiling in the Power Query Editor to get insights into column distribution, value distribution, and more.

4. Creating Validation Reports

You can create a validation report in Power BI that includes visuals like tables and KPIs to display the results of your data validation measures. For example, you can create a table visual to list duplicate records and a KPI visual to show the total number of records.

5. Automating with Power Automate

Power Automate can be used to schedule and automate data validation processes. You can create a flow that refreshes your Power BI dataset and sends a summary report via email.

Example Workflow

1. Create DAX Measures: Define measures to count total records and identify duplicates.
2. Build Validation Queries: Use Power Query to clean and validate data.
3. Enable Data Profiling: Use data profiling tools in Power Query to assess data quality.
4. Design Validation Report: Create a Power BI report with visuals to display validation results.
5. Automate with Power Automate: Set up a flow to refresh the dataset and distribute the report.

By following these steps, you can automate your data validation process and generate comprehensive reports in Power BI. For more detailed guidance, you can refer to resources like Esbrina’s blog on data validation with Power BI and Poespas’ blog on using data validation in Power BI dashboards12.

If you have any specific questions or need further assistance, feel free to ask!

[Kmanikanta]

Thanks for the solution, Could you please eloberate on automation with power automate with example.