Exporting Power BI Report Data to CSV and HTML: A Step-by-Step Guide

Introduction

Power BI is a powerful tool for data visualization and analysis, but sometimes you need to share your report data in different formats. In this comprehensive guide, we'll walk through the process of exporting Power BI report values and visuals to CSV and HTML formats, then sending them to other users using Power Automate.

Prerequisites

Before we begin, make sure you have the following:

• Power BI Premium capacity license or Fabric F64 (or higher) license
• Access to Power Automate
• Basic understanding of DAX queries

Step 1: Prepare Your Power BI Report

Start by opening your Power BI report and selecting the visual you want to export. For this example, we'll use a table visual with columns for username, region, and sales.

Step 2: Use Performance Analyzer to Extract the DAX Query

1. Open the Performance Analyzer from the View menu or the new left-side menu.
2. Start recording and refresh your visual, or click "Analyze this visual."
3. Once analyzed, click "Copy query" to get the DAX query for your visual.

Step 3: Refine the DAX Query (Optional)

If you want to customize the output:

1. Open DAX Studio or a similar tool.
2. Paste the copied query and modify it as needed.
3. Run the query to verify the results.

Step 4: Set Up Power Automate Flow

1. Create a new flow in Power Automate.
2. Add the "Run a query against a dataset" action.
3. Select your workspace and dataset.
4. Paste your DAX query into the query text field.

Step 5: Create CSV and HTML Outputs

Add the following actions to your flow:

1. Create CSV table:

   • Set "From" to "First table rows" from the query result.
   • Choose between "Automatic" or "Custom" columns.
   • If using custom columns, define them using expressions like item()['ColumnName'].

2. Create HTML table:

   • Configure similarly to the CSV table action.

3. Create JSON output (optional):

   • Use "First table rows" from the query result.
   • You may need to define the schema based on the output.

Step 6: Save and Share the Data

1. Add a "Create file" action to save the CSV output to SharePoint:

   • Set the file name and content (use the body of the CSV create table action).

2. Add a "Send an email" action:

   • Set the recipient, subject, and message body.
   • Include the HTML table output in the email body.

Step 7: Test and Run the Flow

1. Save your flow.
2. Click "Test" and choose to run it manually or automatically.
3. Check the output in SharePoint and your email inbox.

Advanced Techniques

Customizing the DAX Query

You can modify the DAX query to:

• Filter data
• Add calculated columns
• Change the sort order
• Limit the number of rows returned

Example:

EVALUATE
SUMMARIZECOLUMNS(
    'Table'[Region],
    'Table'[User],
    "Sales", SUM('Table'[Sales])
)
ORDER BY 'Table'[Region], 'Table'[User]

Using Variables in Power Automate

You can use variables in your flow to make it more dynamic:

1. Add an "Initialize variable" action at the beginning of your flow.
2. Use the variable in your DAX query or other actions.

Example:

EVALUATE
FILTER(
    SUMMARIZECOLUMNS(
        'Table'[Region],
        'Table'[User],
        "Sales", SUM('Table'[Sales])
    ),
    'Table'[Region] = @{variables('SelectedRegion')}
)

Scheduling the Export

To automate the export process:

1. In Power Automate, change the flow trigger to "Recurrence."
2. Set the frequency (e.g., daily, weekly, monthly).
3. Optionally, add conditions to run the flow only on specific days or times.

Troubleshooting Common Issues

Error: "The query contains unsupported elements"

Solution: Simplify your DAX query or break it into multiple steps using variables.

Error: "The dataset is not in Import mode"

Solution: Ensure your dataset is in Import mode or use DirectQuery mode with supported data sources.

CSV or HTML output is empty

Solution: Verify that your DAX query returns data and that you're using the correct column names in Power Automate.

Best Practices

1. Optimize performance: Limit the amount of data exported by using filters in your DAX query.
2. Secure your data: Use Power Automate's built-in security features to protect sensitive information.
3. Test thoroughly: Always test your flow with various scenarios before deploying it to production.
4. Monitor usage: Keep an eye on your Power BI capacity usage, especially when running frequent exports.
5. Document your process: Maintain clear documentation of your export flows for easier maintenance and troubleshooting.

Limitations and Considerations

• This method works best for tabular data. Complex visuals may require additional processing.
• Large datasets may impact performance. Consider using incremental refresh or partitioning for better performance.
• Some Power BI features, like RLS (Row-Level Security), may not apply to exported data. Ensure you handle security appropriately in your flow.

Conclusion

Exporting Power BI report data to CSV and HTML formats using Power Automate offers a flexible way to share information with users who may not have direct access to Power BI. By following this guide, you can create automated flows that deliver the right data to the right people in the format they need.

Remember to regularly review and update your exports to ensure they continue to meet your organization's needs and security requirements. With practice, you'll be able to create sophisticated data sharing solutions that leverage the full power of Power BI and Power Automate.

Additional Resources

• Power BI Documentation
• Power Automate Documentation
• DAX Function Reference
• Power BI Community Forums

By mastering these techniques, you'll be able to create more versatile and user-friendly reporting solutions that cater to a wide range of data consumption needs within your organization.

Article created from: https://youtu.be/nrOS9Y5M55M?si=c8I-OFFW41zzjCaR