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!