The blog post been guest written by our great partners over at OPSPro, LLC. Cathy Goin, Tricia Santos, and their team have been instrumental in helping us develop the ability to connect PROCAS data to Business Intelligence systems via an API. To learn more about the relationship between API and BI, click here for Part 1.
Download – OPSPro Demo of PROCAS Data Using PowerBI.pdf (1.19 mb)
Connect and Transform PROCAS Data – Power Query in Excel
Once you have Procas API access setup, there are a few things you will need to do. You will need to connect using Power Query in Excel or connect to Power BI. Power Query in Excel is a lighter version of the Power BI desktop application.
With Power Query, you can:
- Connect to the data
- Merge with other data sources
- Transform pivot or unpivot
- Remove or add columns
- Apply custom formula’s
- And so much more
With the shaped data, it can be loaded into your excel file as a table. This method will provide you with an introduction to many of the data mining methods used in Power BI but in a format, you might be more familiar with.
Reasons to Explore the Power Query in Excel:
- Provides a great way to validate data in a macro table view. You can apply custom cell formatting to make data that does not validate correctly appear in bright red.
- Gets you out of the cell method and into the column method. Rather than focusing on the formula of a single cell and then replicating either across or down. Power Query is all Column-based logic.
- Using Power Query’s column-based logic it can be easier for you to check your math and reduce formula errors.
- You can refresh the data when you open the file. It will keep all your steps and transformations.
- You can apply Pivots and charts.
- Combine and merge data from other sources such as; SharePoint, Excel, and hundreds more.
Some limitations:
- Not all connectors are available.
- The data must be refreshed manually.
- Changes made to any cells inside a table loaded from Power Query will be reset to reflect what is in the data source on refresh.
Upgrading your Reports
Power BI Desktop application and Power BI Service are the true powerhouses behind this self-service reporting tool. Not only does it provide a way to format and clean your data, but it also delivers these engaging, interactive visualizations to tell a story or highlight critical decision-making information.
In addition to all the Power Query features in Excel, there are additional connectors and additional methods to handling formulas:
- Using DAX formulas to create calculated columns, measures, and dynamic formatting.
- Bookmarks and interactive buttons
- Customizable visuals and a visual marketplace
- Custom slicers and hierarchies
- Custom report pages and dashboards
- Change the interaction between visuals.
- Schedule refresh with the Power BI service.
- Restrict access using Row-level security or RLS.
Tips and Tricks:
- You can copy your query steps between both systems naturally because they all use the M Query language.
- Many Excel formulas have equivalent formulas in DAX.
- Have a goal or question you want answered before beginning a report or data set. Start off narrow and simple and build the report up in pieces.
Leveraging PROCAS Data via the API
Data mining and cleaning your data can be daunting. OPSPro can help. OPSPro has experience connecting and combining many different types of data sources including SharePoint lists, SharePoint Files, OneDrive files, Office 365 Admin Centers, Trello, Jira, Excel, and many more.
OPSPro can also design custom export solutions using Microsoft Flows and SharePoint. OPSPro received some of the earliest access to the Procas API so we are ready to help you navigate. Our services range from consulting to full outsourcing services.
Contact OPSPro, LLC to schedule a demo today!
Download – OPSPro Demo of PROCAS Data Using PowerBI.pdf (1.19 mb)
Resources
Getting started – Power BI Microsoft Docs
Introduction to DAX – Power BI Microsoft Docs
