How to Analyze Data with SharePoint and InfoPath Using Power BI

Learn how to analyze data with SharePoint and InfoPath using Power BI or Power Query in this step-by-step instructional post.

System requirements:

  • SharePoint Server; SharePoint Online
  • Excel 2010 and beyond Or Power BI
  • InfoPath Designer 

Despite Microsoft’s attempts to retire InfoPath from the newest versions of SharePoint, the form designer tool persists and is still widely used across SharePoint sites.

As we all know, its popularity is due to the ease of use and the frequent need to present e-forms with built-in intelligence without code.

Repeating tables are one of the popular features of InfoPath. The challenge however is to extract the content of the tables for reporting and analysis.

This can be achieved using Power Query or Power BI and the form library as a data source.

The example InfoPath form below contains a repeating table or repeating section.

InfoPath form


When publishing the form to the SharePoint library, using the publishing wizard, the repeating table fields should be published as merged columns of the form library. You should only add the columns that will be required to create the required report. Our goal here is to create a report, graphic or otherwise in Excel or Power BI that will look like this using all 3 columns of our repeating table.

Excel repeating table


Publish all 3 columns using the setting shown below.

publishing wizard


Once published, the data contained in the repeating table will be shown as in the picture below.

SharePoint repeating list


In order to analyze the data as single separate entries, we need to create a connection to the document library using Power Query.

Once connected to the SharePoint Library in Power Query, we need to split the merged values between the Item Name, Number and item Classification columns into separate rows.


Power Query


We add 3 new columns using the following formula in a new column of type table using the Text.Split function:

add custom column


This formula will create the following custom column:

custom column


The next step in the query is to expand the custom column we just created:

expand custom columns


After expanding the column, each entry is added as a row:

entry as a row


The last step is to clean the query by deleting the original columns and any other steps required to obtain the query needed for the report.

final report

Related Articles to Help Grow Your Knowledge

Features and Benefits of Microsoft 365
Features and Benefits of Microsoft 365

Microsoft 365 or Microsoft Office 365 has all the apps that help you reach your business or educational goals. Learn more about the features and benefits of Microsoft 365 that will help you achieve those goals efficiently. This guide will also help those who have...

Automating Your Tasks with Teams and Power Automate
Automating Your Tasks with Teams and Power Automate

Microsoft Teams is a collaboration platform that combines all the tools your team needs to be productive. MS Teams is an ideal platform to enhance productivity and help you accomplish more things. With its powerful chat features, you can easily stay in touch with your...

Do You Need Power Automate Dataverse Integration?
Do You Need Power Automate Dataverse Integration?

Part of the Microsoft Power Platform, Power Automate (formerly known as Microsoft Flow) is a must-have tool for any business. Power Automate can help you increase business productivity by automating routine processes and tasks, freeing your employees to focus on...