SharePoint & InfoPath: Analyzing Data 

  • By IncWorx Team
  • 16 Aug, 2017
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 data source.

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

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.
Publish all 3 columns using the setting shown below.
Once published, the data contained in the repeating table will be shown as in the picture below.

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 in the Item Name, Number and item Classification columns into separate rows.

We add 3 new columns using the following formula in a new column of type table using the Text.Split function:
This formula will create the following custom column:
The next step in the query is to expand the custom column we just created:
After expanding the column, each entry is added 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.

      The IncWorx SharePoint Consulting Blog

      By IncWorx Team 22 Sep, 2017

      If you are implementing SharePoint in your organization for the first time, or you are planning to upgrade your current SharePoint environment, you have likely questioned whether to use Office 365 and SharePoint Online versus an on-premises installation of SharePoint Server. To make the decision easier, below are five important factors to consider:

      By IncWorx Team 16 Aug, 2017
      Learn how to analyze data with SharePoint and Infopath using Power BI or Power Query in this step by step instructional post.
      By IncWorx Team 27 Jul, 2017

      SharePoint support isn’t only about fixing something that's broken, in fact, it is more about pro-active monitoring, training, development, and demonstrating how to use SharePoint. When you connect with a partner that provides a holistic approach to support, you will see what a big difference it can make. SharePoint Support should be available when you need it most or when you just have a quick question. With the right support your organization can benefit immediately.  Here are 5 major signs you are in need of expert SharePoint support services:

      1. Still sending attachments by email? This very popular way of sharing documents, while familiar, is no longer considered a good practice and with SharePoint sharing and collaborating on documents is a powerful core feature, making sending documents by email a thing of the past.

      2. Feeling like you’re on an island?  In many organizations there is that one person you can count on to answer your technical questions, but what happens when that person isn’t available or doesn’t exist?

      3. Is time really money? when your time is being spent trouble shooting technology or trying to figure out how to use SharePoint what are you gaining? You are valuable for the role you play in your organization, technologies like SharePoint exist to support and increase your value not slow you down.

      4. Are you using SharePoint like a shared drive? Similar to sending emails as attachments, using SharePoint like a shared drive also remains popular. Making folders to organize documents is the way things used to get done, with a little training and support the powerful document management capabilities built into SharePoint will take document management and sharing to the next level.

      5. How do I...? With any new technology, we all feel a little lost. Grasping the concept of SharePoint can be a challenge, but once you figure it out the opportunities to create value for you, your team and your organization are endless but how do you get there?

      By IncWorx Team 27 Jul, 2017

      SharePoint is an incredibly powerful tool within the Microsoft stack, that when implemented correctly, can streamline and solve many business problems. The stumbling block that many people run in to is that working with the platform requires a fairly specialized skillset, and a general ‘IT’ background doesn’t necessarily translate in to being able to pick up and learn SharePoint and begin optimizing your entire company right out of the gate. In fact, given the complexities of the platform and the various other Microsoft technologies that it touches, it can take a very very long time to become an expert. If you have all of the time in the world to set aside and learn SharePoint, stop reading, but if you have many other things to do and are looking to accelerate your organization, here are 3 key ways to leverage a SharePoint Consultancy to take things to the next level.


      1)    Business Analysis and Roadmapping

      As mentioned, SharePoint is a massively complex platform that does so many things that can add value. Think of it as a deluxe Swiss Army knife. The majority of people out there only use the knife (document storage), but don’t use many of the other tools because they’re not 100% certain of how/where to use them. A good SharePoint Consultant can come into your organization and meet with key business units and have non-technical discussions to understand what they do, how they do it, and what they struggle with. This is where the “I know SharePoint does this, but I don’t know how” comes in to play. A consultant can bridge that gap for you and identify key areas that you know are pain points and roadmap a solution. Not only that, they can also identify other areas that SharePoint is REALLY good at, that can empower your team even more and help you understand how to get there.


      2)    Regular Care and Feeding

      Time is the most valuable thing to most everyone. No one has enough of it, especially not IT people. There are countless things on your plate each and every day and new things that pop up out of nowhere. Given the complexities of SharePoint, it is a machine that will take care of you if you take care of it.  The problem is, taking that daily vitamin is hard to follow through on and remember when you’re running around with your hair on fire every day. Most people get anxiety about wondering when their SharePoint farm is going to break if they don’t have the time to spend on the regular care and feeding.  Unfortunately, this area is really low visibility, and often times falls lower on the priority list than keeping business units happy. Outsource this to someone like an IncWorx SharePoint Consultant, and stop worrying about it - focus on adding value to your business. You will thank us later when you do!


      3)    System Consolidation

      I have mentioned a few times that SharePoint is really complex, there isn’t enough time in the day, and IT people run around with their hair on fire. One way to take advantage of SharePoint’s massive set of features and functionalities and alleviate the time/stress issues associated with day-to-day IT life is to reduce the number of systems that you’re running in your business. More moving pieces equals more things that can break and more phone calls that you get while you’re on vacation. SharePoint does so many things, if you work with a knowledgeable SharePoint consultant on a consolidation strategy, you will likely identify a number of various systems that can be replicated and replaced by SharePoint using out-of-the-box features and functionality. This means if you work with someone good, and have a really good handle on proper planning, maintenance, and support, you have a singular item to worry about. Essentially, if you follow step 1, implement step 2, and then move to step 3 – your general quality of life as an IT pro will be ridiculously better! P.S. IncWorx SharePoint consultants can help you with all of the above.

      By IncWorx Team 21 Jul, 2017

      This year, more than any I can remember, we seen an extraordinary amount of headlines like this:

      “World’s biggest cyberattack sends countries into ‘disaster recovery mode’”-5/14/17 CNN

      Or statistics like this from Steve Morgan, Editor-In-Chief at Cybersecurity Ventures, “Cyber crime damage costs to hit $6 trillion annually by 2021."

      If that isn’t enough to send chills up any tech employees spine, I don’t know what is. So what are some best practices?

      Use more than one requirement to log on. Sometimes as simple as 2 factor authentication (something you have and something you know) can go a long way.

      Secure Tools & Networks
      Utilize a tool like SharePoint to share documents. It is much easier and safer to utilize a closed network.
      Engage an expert to manage your settings. As simple as this sounds, there are many companies that I have encountered that have capabilities within SharePoint to better secure themselves that either do not know they have them, or do not know how to properly use them.

      Keep up to date with any updates or patches that a company like Microsoft puts out. As hackers change their tactics, company gatekeepers need to know what threats are out there.

      Make sure your internal staff has the correct credentials. Often times we see SharePoint environments where too many people have admin access and are making changes that shouldn’t occur, making the environment vulnerable. With the correct provisioning, this can easily be avoided.

      These are just some basic examples that everyone should be looking at. Unfortunately, in my business I hear far too often that the client either doesn’t have the expertise, or time to run specific checks and patches on a daily basis. If that sounds familiar, I would highly recommend reaching out to IncWorx and scheduling a quick call to see how we can help in any given situation. This is what we do all day everyday and we would be happy to discuss how to get your systems secure before the next breach happens. 

      By IncWorx Team 19 Jul, 2017
      Maximize your SharePoint investment by getting the most out of it. You harness the true power of SharePoint when you leverage its customizability to meet the specific needs of your organization.
      Share by: