{"id":171358,"date":"2023-08-03T12:43:02","date_gmt":"2023-08-03T11:43:02","guid":{"rendered":"https:\/\/liora.io\/en\/?p=171358"},"modified":"2026-02-19T17:54:52","modified_gmt":"2026-02-19T16:54:52","slug":"excel-to-power-bi-how-to-transform-a-pivot-table-in-excel-into-a-dataset-that-can-be-used-by-power-bi","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/excel-to-power-bi-how-to-transform-a-pivot-table-in-excel-into-a-dataset-that-can-be-used-by-power-bi","title":{"rendered":"Excel to Power BI: how to transform a pivot table in Excel into a dataset that can be used by Power BI?"},"content":{"rendered":"\n

Do you want to leverage data with Power BI, especially from your Excel files? Check out our tutorial on how to transform pivot tables into actionable datasets.<\/strong><\/p>\n\n\n\n

When you want to work with data in Power BI<\/a>, you have various options for your data sources. Among them is the ability to work with your Excel files, making it easier to automate and exploit the data within them.<\/p>\n\n\n\n

However, often the format of your data in the spreadsheet is not suitable for direct use in Power BI<\/a>. Indeed, Microsoft’s Business Intelligence software only accepts data stored in the form of tables. This terminology refers to data organized in the form of tables, where columns represent categories of information (phone number, date, time, text, integer\/decimal numbers, etc.), and rows represent data records or entries.<\/p>\n\n\n\n

Ex :<\/p>\n\n\n\n

\n \n \n \n \n \n \n
Name<\/em><\/strong><\/th>\n First Name<\/em><\/strong><\/th>\n Age<\/em><\/strong><\/th>\n Profession<\/em><\/strong><\/th>\n <\/tr>\n <\/thead>\n
MOREAUX<\/em><\/td>\n Antonin<\/em><\/td>\n 27<\/em><\/td>\n Developer, Writer, Trainer<\/em><\/td>\n <\/tr>\n
FELLOUS<\/em><\/td>\n Aur\u00e9lia<\/em><\/td>\n –<\/td>\n Head of Marketing<\/em><\/td>\n <\/tr>\n <\/tbody>\n <\/table>\n<\/div>\n\n\n\n\n
<\/div>\n\n\n\n

However, this data organization method is not always the most visual. Many Excel users prefer to organize their tables in their own way. As soon as this data needs to be used to create dashboards, it quickly becomes challenging to utilize these resources.<\/p>\n\n\n\n

For example:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

In this article, we will detail the acquisition and transformation of an Excel file in the form of a “pivot table” before using its data to create a complete dashboard.<\/p>\n\n\n\n

Acquisition of an Excel file<\/h2>\n\n\n\n

As mentioned above, Power BI accepts various data sources, including the spreadsheet software from the same creator: Microsoft Excel. In this section, we will see how to load an Excel file into Power BI.<\/p>\n\n\n\n

1. Loading a new data source<\/h3>\n\n\n\n

In the “Home” tab, you will find the following tools:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Click on “Excel Workbook,” and your file explorer will open. From there, you can select the file you want to use.<\/p>\n\n\n\n

Once you’ve done that, a new contextual menu will appear:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

This menu allows you to select the item you want to interact with. With Excel, Power BI recognizes sheets as well as tables declared in Excel. You can differentiate these elements through the associated logos:<\/p>\n\n\n\n

\n
\n

– Sheets:<\/p>\n<\/div>\n\n\n\n

\n
\"\"<\/figure>\n<\/div>\n<\/div>\n\n\n\n
\n
\n

– paintings :<\/p>\n<\/div>\n\n\n\n

\n
\"\"<\/figure>\n<\/div>\n<\/div>\n\n\n\n

Bear in mind that if you select a sheet, Power BI will load all the data present, not just the selected table.<\/p>\n\n\n\n

Once you’ve selected the desired item, you have two options:<\/p>\n\n\n\n

    \n
  1. Load the data directly into Power BI Desktop<\/a> and start creating visuals.<\/li>\n\n\n\n
  2. Transform your data using Microsoft’s data transformation engine, Power Query.<\/li>\n<\/ol>\n\n\n\n
    \n