Outputting to Power BI from Alteryx: From Transformation to Visualization

Do you use Alteryx to transform data? Do you use Power BI to build reports?

As data professionals, it is important to remain curious and continuously look for new ways to make processes more efficient. The transfer of data and integrations between systems are ripe opportunities for process improvement. At RSM, it is very common to have data delivered in a messy format not suitable for a visualization tool. Power BI is a commonly used business intelligence tool; it is an extremely valuable tool for displaying insights as it allows end-users to develop reports and visualizations without having to code. Alternatively, Alteryx is low-code tool that is capable of more robust data transformations and can be used as an ETL tool. We often find these tools can be used in conjunction to create report-ready datasets to ultimately develop reports that display valuable analytics.

If only there was an easy way to streamline a connection between these two tools!

Streamline Power BI and Alteryx

Imagine you have some transactional data to you need to reformat. For example, say you want your dataset to transform as seen below:

Input – Initial Dataset

Workflow

Output Dataset

Formatting and Visualizing Data

Now that the data is cleansed and formatted using Alteryx, it’s time to visualize that data and build our reports by importing it into Power BI. While you can connect Power BI to a flat file (.xlsx, .csv, etc.), the data can be exported directly from Alteryx to Power BI Service with the Alteryx Power BI Output tool. The Alteryx Power BI Output tool allows you to update a dataset directly from the Workflow using the Power BI REST API. Before doing this, an application must be created and configured in Azure Active Directory with permissions to access the desired Power BI instance.

If you are using both Alteryx and Power BI in conjunction with each other, using this output connector as opposed to a static CSV file may be new to you. Within this tool, there are a few limitations & issues that need to be addressed:

1. There is only one table that may be published per dataset, per Alteryx Power BI Output tool. The ability to output multiple tables is not supported currently by the Power BI Output tool. If you are looking to publish more than one Power BI table within your Alteryx Workflow, you must add multiple Alteryx Power BI Output tools to the workflow and publish each table under its own dataset.
2. By default, Power BI will detect numeric columns and set the summarization property to sum or count.
3. Power BI will not allow you to upload a table that has more than 75 columns.

It is also important to note that since this tool is not automatically installed with Alteryx Designer, you must download it from the Alteryx Analytics Gallery. Download here: Power BI Output

Azure Active Directory Setup

1. Navigate to https://dev.powerbi.com/apps and sign in with your Microsoft Azure Active Directory Account
2. To configure the application, you must:

• • Enter an Application Name
  • Choose “Server-side web application (for web apps or web APIs)” as the App type
  • Enter the Home Page URL. This is the Power BI Workspace that your dataset will be posted to (i.e. https://app.powerbi.com/AlteryxtoPowerBIDemo).
  • Enter the Redirect URL: https://login.live.com/oauth20_desktop.srf

3. In the Choose APIs to Access, enable Read all datasets and Read and write all datasets. This way, the Power BI Output Tool will write the data to your specific Power BI instance; it will also check if it may overwrite certain datasets.
   
4. Click the Register App button

• • Once you do this, you will be prompted with a window that states your application has been registered. Be sure to save the Application ID and Application Secret values securely (you’ll need them later).

Tool configuration within Alteryx

1. Enter the Tenant ID, which is the identifier for the Azure Active Directory tenant associated with the Power BI instance created.
   • Log into Microsoft Azure Active Directory from the Azure Portal
   • In the menu on the far left of the page, Click App Registrations then find and click on the app you just created.
   • Copy and Paste the Directory (tenant) ID listed at the top of the page
     
2. Enter the Client ID
   • This is the Client ID that you stored after creating the app. However, this is also listed in the Active Directory page in your app registration under “Application (client) ID”
3. Enter the Client Secret that was given to you upon registering the Power BI app
   
4. Next, choose the authentication type (Refresh token or Persist credentials) that you’d like to use to be able to run the Power BI Output tool
   • If Refresh token is selected: Once credentials are entered, the tool will remain logged in until the Refresh token expires or is revoked.
   • If Persist credentials is selected: Once credentials are entered, the tool will remain logged in until your user credentials are changed
5. Next, click the Login button
6. On the next configuration screen, select the Workspace that you’d like the dataset to populate from the dropdown menu.
   
7. After selecting a Workspace, you will see a checkbox labeled “Enter custom dataset and table names”. If you already have a dataset and table name for this data in the Workspace, leave this unchecked. If you are creating a new dataset, be sure to select the checkbox.
8. Select/Create a dataset name of your choice
9. Select/Create a Table name of your choice
10. Insert a batch size number. Batch size corresponds to the number of rows that are imported into Power BI. The default batch size is 500.
11. Existing dataset and table behavior – If using an existing dataset, select one of the following from the dropdown menu:
    • Append rows to table
      • If the dataset and table exist, these rows will be appended to the table
      • If the dataset exists, but does not contain the table, the workflow will halt with an error message
      • If the dataset and table don’t exist, they will be created here
    • Create new dataset
      • Creates the specified dataset and table if they do not exist
      • If the dataset exists, the workflow will rename the dataset
    • Replace existing dataset
      • • If dataset doesn’t exist, this creates the specified dataset and table
        • If the dataset exists, the dataset and all of the tables within it will be deleted and a new dataset/table with the same name will be created
12. Lastly, select apply, click on the canvas to save the configuration then run the workflow.

Now, after navigating to Power BI Service, you should see your dataset listed. Select the 3 vertical dots and select create report:

You can now begin building reports based off your new dataset:

Leveraging the Alteryx Power BI Output tool to reduce the number of manual steps needed to update a dataset is a great example of streamlining a data and analytics process. The same result is achieved as populating an excel workbook and connecting it to Power BI Service, but now our data workers have more time to spend deriving insights and making meaning of their data.

If you have any questions about this process, advanced analytics, or Power BI and Alteryx in general, please visit our website, call 800-274-3978, or contact us.

Evan Hopper