You can now extract more from your business data by using Microsoft’s main analytics platforms together.
Businesses need data, and for many years Microsoft’s Excel has been key to analysing and using that data. The spreadsheet has been the go-to tool for business analysts thanks to its flexibility and its extensibility, with formulas and macros that make it an analytical playground.
However much of that torch has passed to the Power Platform and to Power BI (originally a set of Excel tools that became its own product). But it’s always been an amicable split, with the two tools working well together and using the Power Query tooling as a common data extraction, transformation, and load pipeline. Using those tools you can go from almost any data source to either a Power BI report or an Excel spreadsheet, depending on what you want to do with your data.
Work with Organisation datatypes
With Power BI now part of the Power Platform suite of business automation tools, it has gained deeper links to some of the underlying technologies in Microsoft’s Dynamics 365 line-of-business software tools. This gives you access to the tools needed to build and name organizational datatypes, with Power BI used as an authoritative source. You can now have specific datatypes for key pieces of business data, using Power BI featured tables to share datasets with Excel.
The ability to transfer data between the two tools is important. While Power Query in Power BI can help make complex queries easy, it’s focused on visualisation. That’s a powerful tool, but getting it right does require some expertise. It’s too easy to choose the wrong values for axes or the wrong plot type, resulting in the wrong conclusion. If you have experience with Excel and its tools and services, linking it and Power BI makes a lot of sense, as you can produce queries and formulas in Excel that can be published to Power BI to construct dashboards and visualisations that take time to produce in Excel.
Power BI has the option of creating featured tables from datasets it’s using. Simply select a table and set it to be a featured table. Start by giving it a description, a row label and a key column to use as a unique ID. If you treat Power BI as your initial data hub, you can use it to pre-process data (especially with its links to Azure’s machine-learning Cognitive Services) before using Excel for deeper analysis. Data is published into a Power BI workspace, where it can be downloaded.
Modern line-of-business systems use aggregate datatypes to simplify operations, with a customer field containing multiple sub-fields, including name and address. You can take this data, bring it into an Excel table, along with data from other sources, and explore it using familiar tools and formatting, producing insights and algorithms that can be used elsewhere in your business.
As Power BI supports Microsoft’s information protection tools, you can lock down commercially sensitive information so that only approved users get access. As it (and the related Common Data Service) give access to much of your business’s critical information, it’s important to get this right, as having too much open can make it easy for attackers to access confidential and personal information.
Organisation data support goes both ways: with Excel and Power BI connected, you can create a new Excel spreadsheet, making its content a table; then select some text fields and choose to convert to a datatype from your organisation. Excel populates this dropdown with up to 10 Power BI datatypes, ready for use in your code. Choose the ‘More from your organisation’ option to see all the available datatypes and datasets.
Using custom datatypes from Power BI in Excel Power Query
One important new feature is support for Power Query datatypes in Excel’s data transformation tools. If you’ve used Power Query in Power BI, you should be familiar with its capabilities, working with large data sets quickly and easily. Bringing this into Excel simplifies the process of taking complex data and turning it into spreadsheets, with support for complex datatypes.
Using Power Query is easy enough. In the Excel data import tool, bring in a file and start using the built-in Power Query editor to start building your import query. Select the columns you want to use and then click the Create Datatype button as part of your data transformation. You can then give that custom datatype a name and add a column.
You can use this feature to create, say, a product datatype that brings together name, SKU, price, and any other related columns in your original source. That complex datatype is collapsed into a single column in the resulting data grid, ready for use once you load the data into your spreadsheet. You save space in a view with an expanded card view of the data in your new custom type, making it easier to navigate your spreadsheet, with all the underlying information still available for use in analytical queries.
You can even use the contents of custom rich fields in formulas, using them to extract individual elements from a field for use in additional calculations. You can think of custom datatypes like this as a way of simplifying how you view information in Excel, without changing how you can use it. You do need an Office 365 subscription to use this new tool, as it’s part its regular rolling updates to Excel.
Linking Excel to Power BI datasets in this way links the two analytic tools in new ways, as you can quickly refresh your Power Query export to update your spreadsheet. While that might not be the same as watching live data in a Power BI dashboard, it gives you the confidence to know that your Excel analytical applications have the most up-to-date data.
Use the same data, everywhere
Breaking down the silos between data analysis tools is important, and using common datatypes goes a long way to erase those differences. If we’re working in Excel or in Power BI, we need to know that we’re using the same data and that we can share any resulting analysis without worrying about confusion or misunderstanding. Datatypes are labels, and having the same labels all across businesses not only helps us work together more effectively, it also allows new technologies like machine learning to get the most from our data.
