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

Microsoft Teams Adoption Strategy: 5 Critical Considerations
Microsoft Teams Adoption Strategy: 5 Critical Considerations

Microsoft Teams is growing at an incredible rate. In October of 2020, Microsoft reported a 50 percent rise in daily active users from the numbers six months prior. More and more IT business strategies include implementing Teams as a hub for communication and...

Microsoft RPA: Get More Done With Robotic Process Automation
Microsoft RPA: Get More Done With Robotic Process Automation

Every business runs into mundane, time-consuming tasks every now and then. Maybe your team members keep repeating the same navigation steps, clicking the same buttons, just to pull a report. Or perhaps they're constantly forced to look up information in one app so...

Is Power BI Good For Big Data?
Is Power BI Good For Big Data?

The rise of Power BI from a little-known platform to one of the most powerful business analytics tools in the world is nothing short of spectacular. In just under seven years, Power BI has accrued more than 5 million subscribers, a feat that lays testament to just how...

Join over 1,000 people who receive insights, guides and advice for Microsoft technologies every month.