Are you learning Power BI from the beginning? I will show you here, step by step, how to create a report in Power BI from Excel. I will use the pie chart, stacked column chart, line chart, and bar chart in the Power BI report.
To create a report from Excel in Power BI, first, import your Excel data into Power BI by selecting “Get Data” and then choosing “Excel” as the source. After loading the data, use Power BI’s drag-and-drop features to design and customize your report, and then you can use the pie chart, stacked column chart, line chart, and bar chart to present your data effectively.
I will use the Power BI desktop to create the report in this case. So, install the Power BI Desktop on your local system.
Create a Report from Excel in Power BI
Before you jump into creating a report from Excel in Power BI, it’s important to ensure that your Excel file is properly structured and organized. Make the Excel file should have a header, and also, it should have a meaningful table name.
You can do this by selecting the records from Excel and then clicking on the Format as Table option. After that, the “Create Table” window will appear. Then, check if My Table has headers like the screenshot below.
Then, in the Top navigation, Click on the “Table Design” option; now you will see that the Ribbon Table Name field will be available, and you should give a meaningful table name.
Now our Excel is completed, you can use this Excel. For this demo, I am using a sample Excel file from Microsoft that you can also download and use Financial Sample.
Once your Excel file is ready, we can create a report on the Power BI desktop.
1. Open this Power BI desktop; click on Get data -> Excel workbook from the top navigation bar and directly click on the Excel workbook option. Check the screenshot below.
2. This will ask you to browse an Excel file. Here, select the Excel file and click on Open.
3. After that, it will display all the tables in the Excel file you created. Select the table you want to use for creating the report. Click on Load or Transform Data.
- Click the Load button when you have clean data, and you do not want to clean up your data.
- Click Transform Data if you want to format or clean up your data. This is useful when you need to remove a column or filter rows, or when you want to do something as common as using the first row as a table header, etc.
In my case, I am using a Load button.
4. Then, in the right-side “Data” navigation, you will see the table and the corresponding columns that we can use in the Power BI report. Check the screenshot below.
5. On the right-hand side, in the Visualization options, you can see a lot of visuals that you can use in your report to make it attractive. A few visual names are Stacked Bar chart, Clustered Bar chart, Line Chart, Pie Chart, Map, table, and many more.
6. After Clicking on a chart or visual in the Visualization menu at the bottom, you’ll notice available fields. You can add columns to the fields based on your requirements. To add data, simply drag a column of data to the Data navigation and drop it onto the desired field. Check the screenshot below; I have added the PRICE column.
Add Pie Chart to Power BI report
1. I am using a Pie chart to display the Sum of Profit by product. Click on the Pie chart in the Visualizations navigation.
2. Then drag the column data From the Data navigation to the Visualizations navigation. Here, the “Legend” field contains the Product names, and the “Values” field contains the Sum of profit data.
If you expand the Values field dropdown, you will see options like Aggregation like Sum, Average, Minimum, Maximum, Count, Standard deviation, Variance, or Median. Choose one according to your requirements.
Add Stacked Column Chart to Power BI report
In the same way, you can also use a Stacked Column chart to display the Sum of sales by Product and County. The x-axis should contain the Product names, the y-axis should represent the Sum of sales data, and the Legend field should contain the country name.
Add Line Chart to Power BI report
Now, we will create a Line chart to display the Sum of Sale Price by Manufacturing Price. The x-axis should represent the Manufacturing price, and the y-axis should represent the Sum of Sale Price data.
Add Bar Chart to Power BI report
We will create a Bar chart to display the Sum of Units Sold by Segment. The x-axis should represent the Sum of Units Sold, and the y-axis should represent the Segment data.
After creating the report, our complete Power BI report looks like this.
You can save it by clicking on the File menu in the top-left corner and then selecting Save or Save As Option.
Then, choose a location and provide a name for your report file. This will ensure that your work is preserved and can be accessed later.
Conclusion
I hope you can follow the above steps to create a report in Power BI using Excel data. I have explained how to use the pie chart, stacked column chart, line chart, and bar chart to present your data effectively.
You may also like:
Preeti Sahu is an expert in Power Apps and has more than 6 years of experience working with SharePoint and the Power Platform. As a Power Platform expert for Power BI, Power Apps, Power Automate, Power Virtual Agents, and Power Pages, she is currently employed with TSinfo Technologies. She is the author of the book Microsoft Power Platform A Deep Dive. She also made a big technical contribution to SharePointDotNet.com in the form of articles on the Power Platform. She enjoys traveling and spending time with her family in her spare time.