Connecting Technology and Business.

Creating A Power BI Data Model From An Excel Table

I have an Excel table with columns and rows of information formatted as a table. It is a price list of items available for selling for the month. I want to upload this on a Power BI site so that my staff can benefit from the Q & A feature of Power BI. Once the data model in the worksheet is uploaded to the site, whoever wants to search for the price or the part number of one or more items in the list can have this displayed immediately as a table or as a card in a browser. Here are the names of the essential fields apart from the others in the table:

PartNumberPart Number
ItemNameName of the item
ProductFamilyFamily of the product
DTPDTP of the item


I open the Excel workbook containing the worksheet. I click on PowerPivot tab and add this table to the data model by clicking the "Add to Data Model" command. A new Excel window with the Power Pivot table opens up and displays the entire worksheet. (Here, I can rename columns, delete unwanted columns and do some data transformation by adding new columns and thereby establish relationships with the columns).

I am now more interested in the info I am going to provide my staff from this table. So, I go to advanced and click on the Default field set to specify the columns and field order in which it will be displayed while visualizing the data in Power BI. I select the four fields that are listed in the table above.

I am also more particular about the Q & A part of Power BI which will enable them to search for the specific item name and its DTP or Part Number. So, I go to the Synonyms feature and start adding some synonyms for the column header named DTP – Price and Cost are two words that come to my mind immediately. I add them as synonyms.

I save this file.

Now, my data is ready for uploading on the Power BI site.

I go to my BI site and drag and drop this workbook from my local system to the workbooks area. I also add this to the Q & A feature by clicking on the ellipsis at the bottom right corner.

Now, anybody who has access to the Power BI site can click on the Q & A feature at the top right corner of the page and start asking plain English questions on the pricelist that is uploaded. A sample question could be "What is the price of A" and immediately, the site helps me with suggestions to the available list of items and once done, displays the results in the form of a table or a card as required.

Very simple and user friendly indeed!

Using the Power of the cloud to develop applications

Visual Studio Online is the fastest and easiest way to plan, build, and ship software across a variety of platforms. Get up and running in minutes on our cloud infrastructure without having to install or configure a single server.

Unlimited private projects using Git or TFVC

Create private team projects accessible from anywhere. Use Team Foundation Version Control (TFVC) for traditional centralized version control, or Git, if you prefer a distributed approach. You can even mix-and-match on different team projects, making it easy to host your entire organization on a single account.

Track everything in one place

Capture new features, bugs, and or other work items into backlogs that are great for teams practicing Scrum, Kanban, or their own agile process. Use customizable task boards to track team progress, while agile portfolio management enables larger groups to track work across all their teams.

Cloud-powered continuous integration and deployment

Catch quality issues earlier by using build definitions that automatically compile and test your applications in the cloud, either on-demand or after any change in code. Track build health over time with graphs and customizable dashboards. When tests pass, automatically deploy updated websites directly to Microsoft Azure.

Gain insight into your applications (preview)

Know more about your users and potential issues they may encounter with rich availability, performance, and usage information from your apps—whether they run on a device, your server, in Microsoft Azure, a third-party cloud provider, or some combination of these.

Use your favorite development tool

Access your projects, code, and work items through an integrated experience within Visual Studio or through a free plugin for Eclipse. Support for any Git client (including Xcode) and a modern web browser interface ensure that no matter what development tool you use, Visual Studio Online can tailor to your team's needs.

Scalable pricing that's small-team friendly

Teams of up to five are free, so create unlimited private team projects with as many repos, work items, and bugs as you need without any additional costs. Additional users have straightforward pricing, and shared resources like builds and load tests have included monthly amounts that can be scaled as your demands grow.

User Plans

Pick the right user plan for each of your team members based on their needs. There are three user plans offered directly as part of Visual Studio Online, highlighted below. In addition, MSDN subscribers have access to Visual Studio Online at no additional charge as an MSDN subscription benefit. MSDN subscriptions are purchased separately from Azure services.

Visual Studio Online Basic - Includes access to code repositories, planning and tracking tools, team collaboration capabilities, and the Visual Studio Express development environment. The first five users per account with the Basic plan are free.

Visual Studio Online Professional - Includes all of the capabilities available in the Basic plan, plus it also includes use of the Visual Studio Professional development environment. Up to 10 users with the Visual Studio Online Professional plan may be purchased on an account.

Visual Studio Online Advanced - Includes all of the capabilities available in the Basic plan, as well as advanced team collaboration, project planning and tracking tools, and stakeholder feedback requests.

Power BI enabled by Microsoft Online services

​While MS Excel frontends the Power BI offering from Microsoft, the Microsoft Online services extends the Power BI experience to the cloud by providing a platform for sharing, collaborating and searching for information. It also adds mobility to the information by making the business intelligence information accessible from mobile devices through an app.

Power BI 2.png 

The Power BI platform is built on SharePoint technology and as a result brings with it the collaborative flexibility and added security of information with control in the hands of the business decision makers.

BI Sites

BI Sites are built with additional features that will be required by the analysts who build the data models and visualize the data mined using the queries that they have discovered from the databases. Excel workbooks containing the queries and the power views can be easily uploaded (a drag and drop is all that is required to upload a file) and featured visual reports int he form of tables and charts and Powerview dashboards can be presented on these sites. Queries that were used to build the data models can be shared with others on this platform so that they can also use these queries to design other data models related to the queried data. A dynamic relationship can be established between the datamodels on the site with the source databases using a Data Management Gateway (DMG). This will add a touch of real time capability to the BI information presented in the site.

These sites also have features required by the stakeholders of the information so that the BI experience becomes seamless. The HTML 5 enabled web pages make the featured reports interactive. What if? scenarios can be visualised instantly. Forecasting (predictive analysis capability) is built in and the alforithms that come into play for this forecasting feature can be tested for its accuracy (to add to the confidence of the user on the reliability of this forecast) using the hindcasting feature. Animated visual reports using the play axis feature can make the BI info come alive on the web page.

Q & A

One of the outstanding features of the Power BI sites is the Question and Answer feature. This allowes the users to ask any question on the featured data models on the site in plain English and receive answers instantly - whether it be in form of plain individual facts and figures, tables or a choice of charts. This completely eliminates the learning curve (that might come as a deterrent for many as in the case of other complex BI applicaitons) and provides the decision makers with a simple tool to access the BI information.


The BI information for the BI sites are accessible to users from mobile devices like tablets (coming soon to smart phones) eliminating the need for carrying heavy devices of larger form factors to access realtime information. The Power BI Mobile app brings rich BI information to the mobile devices eliminating the need for loging in to a browser all the time afresh everytime some info is required. Also, the BI info made available to the users is visually uniform across any of these devices making it more user-friendly and providing them a seamless experience.

Microsoft & Quadra Press Conference

According to IDC, one-third of current top 20 leaders in the market in any global industry are going to be disrupted by new rivals who can leverage mobility, cloud, analytics & big data.

In order to succeed and thrive in today’s hyper competitive world, small and medium businesses need to think beyond the traditional boundaries of their business. They need to have access to global markets, suppliers, vendors and talent. Elasticity and scalability are the need of the hour – but these must come at an affordable cost. Employees need to be connected, collaborate and innovate better, and in real time. 

Cloud computing technology is the great leveller for businesses today. It enables them to access enterprise class technologies in an affordable, pay-per use model and puts them on par with larger competitors. A study by Boston Consulting Group (BCG) threw up some very interesting insights on the advantages that technology savvy businesses enjoy over their counterparts:

  • They grow revenue 15% faster
  • Workforce grew 2X faster
  • They are 6 times more likely to have international customers
  • Nearly 20% of tech-savvy businesses have employees in other countries

During a press meet at Coimbatore, Quadra and Microsoft executives along with two leading businesses in the region, who had benefitted from their adoption of cloud computing spoke about the need for businesses to be more technology savvy. This was well covered by the mainstream media, links to which are provided below:

Power BI enabled by MS Excel

If you are familiar with Excel, then Power BI from Microsoft will be just a natural extension of your experience with Excel. We know Excel is the best number crunching tool ever since Microsoft Office came into being. It has ruled the computers of Information Workers who wanted to gather data in one place, extracting information out of them using formulae and functions and present them as tables and visualize them as charts. Some went another step ahead to analyze the numbers using pivot tables and other What if analysis tools offered by Excel. Statisticians used the Data Analysis tool pack to dig deep into the data to study patterns and put the data to various tests to pull out hidden information and forecast various business or social outcomes.

The Self-service BI capability offered by Microsoft today capitalizes on these capabilities of Excel and brings the power of Business intelligence into the hands of the ordinary end users.

Microsoft Office 2013 ProPlus or Office 365 ProPlus today has the BI capability in its Excel application. All one needs to do is download the free add ins that are required for this purpose. Power Query, Power Pivot, Power View and Power Maps are the four additional components that are required to add BI capability to Excel.

Power BI 1.png 

Power Query

Power Query helps in discovering data from various data stores and sources - be it proprietary or open source databases and tables. A powerful search capability built in Power Query also helps in discovering data from the public data sets available disseminated in the internet. It can import data from Excel files, .txt or .csv or .xml files. It can also import metadata and links about files in a folder.

Power Query also can pull out queries from various databases like SQL Server, Azure SQL, Access, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase and Teradata. It also can discover data from other data sources like SharePoint list, OData feed, Azure Marketplace, Hadoop file (HDFS), Azure HDInsight, Azure Blob Storage, Azure Table Storage, Active Directory, MS Exchange, Facebook or SAP Business Objects BI Universe.

Power Query also allows users to create their own customized queries.

Power Pivot

Excel today supports 10 million+ of rows of data normally. With the Power Pivot add-in Power Pivot can handle even more. In fact, the data it can handle can match the needs of customers that handle hundreds of thousands of record per day (like in retail chains) or that which handles passenger traffic. Power Pivot helps user build data models by merging building relations between the tables brought inside Excel by Power Query. Relationships are either automatically detected by Power Pivot or can be created by users. Managing the data tables and cleaning the dataset of redundancy or removing the outliers can done here. Transformation of data and adding additional columns with new data or data that have relation to one or more columns of information in the table are made possible by Power Pivot.

Power View and Power Maps

Visualization of data and making it interactive is done by the Power View feature of Excel. Here is where a dashboard of the various aspects of the data model are built for the interpretation of the information produced by Excel. Tables and graphs appear on the same page and make the BI experience richer. Applying filters and sorting info are also possible in this Power View. There is also an animation axis that is provided in the charts created by Power view that brings to life the data in one or more columns of the pivoted tables. If the tables contain geospatial data (names of continents, countries or cities and towns), they can also be plotted on the maps to make the information more meaningful. This also enables the users to relate to the info in the tables in a better way. A 3D view of the maps with animation of data over time helps derive greater insights into the data. An option to create a video of the view is an added bonus feature.

Power BI from Microsoft has more to it than what Excel can do. We will learn about this in the next posting.