I still remember the first time a client asked me to turn their SharePoint sales list into “something they could actually look at.” They had a SharePoint list with 200 rows of order data and an Excel sheet full of regional targets, and nobody could see the story hiding inside either one. That’s the exact situation this guide solves.

Building a Power BI report from a SharePoint list and an Excel file is one of the most common projects I take on for clients, whether it’s a sales team tracking monthly revenue or an operations team monitoring inventory. Both sources live in Microsoft 365 already, so there’s no extra software to install and no messy exports to manage.

By the end of this guide, you’ll know how to pull data from both sources into Power BI Desktop, clean it up, build a simple data model, and put together a working report with cards, charts, and a slicer — using a sales dashboard as our running example.

What You Need Before You Build This Report

Before you open Power BI Desktop, make sure you have these three things ready:

• Power BI Desktop installed. It’s free from Microsoft and is the tool you’ll use to connect to data and design the report.
• Access to a SharePoint list. I’ll show you exactly how to structure this in the next section.
• An Excel workbook with a proper table. I’m using a file called “Order Details” with a table named “AmazonGo” that holds order-level data. If you’re weighing whether to keep your source data in Excel or move it into Power BI entirely, this comparison of Power Query vs Power BI explains where each tool fits.

Set Up the SharePoint List You’ll Report On

Before connecting anything to Power BI Desktop, you need a SharePoint list with columns that actually support reporting. If you’re building this list from scratch, this guide on SharePoint list examples is a good starting point for the basics.

For this walkthrough, I’m using a list called Sales Data. Here’s exactly how I set up the columns and why each one matters:

Pro Tip: I’ve found that using Choice columns for fields like Region, instead of free text, saves a lot of cleanup work later. Free text lets someone type “north,” “North,” and “N. Region” for the same value, and each variation shows up as a separate slice in your charts.

Once your columns are set up, add a handful of sample rows so you have something to test the connection and visuals with. Here’s the sample data I used for this list:

You don’t need hundreds of rows to follow along — five to ten realistic rows are enough to build and test every visual in this guide.

Connect Your SharePoint List to Power BI Desktop

A connector is just the built-in tool Power BI uses to pull data from a specific source. Here’s how to use the SharePoint one:

1. Open Power BI Desktop. On the Home tab, select Get data, then choose More.
2. In the Get Data window, select Online Services, choose SharePoint Online List, and click Connect.
3. Enter your SharePoint site URL — not the list URL, just the site itself (for example, https://yourcompany.sharepoint.com/sites/Sales). Under Advanced options, choose Implementation 2.0 and pick a View Mode. I usually select Default, which pulls the columns from the list’s default view.
4. Sign in with your Microsoft 365 account if prompted.
5. The Navigator window shows every list and library on that site. Select the list you need — in my case, Sales Data — and preview the data on the right.

Pro Tip: I’ve found that clicking Load straight away almost always causes problems later. Even if the data looks clean in the preview, click Transform Data instead so you can catch blank rows, mismatched data types, or leftover test entries before they reach your report.

Clean Up the Data in Power Query Editor

Clicking Transform Data opens Power Query Editor, where you fix data issues before they hit your model. This is where I remove that stray blank row that SharePoint lists often generate at the bottom of an export.

1. On the Home tab, expand Remove Rows, then choose Remove Bottom Rows.
2. Enter how many rows to remove — usually just 1 for that trailing blank row — and click OK.
3. Check the row count at the bottom of the screen to confirm it dropped as expected.
4. Once the data looks right, go to the File tab and select Close & Apply.

Your cleaned SharePoint data now sits in the Data pane on the right side of Power BI Desktop, ready to use.

Connect Your Excel Workbook to Power BI Desktop

Now let’s bring in the Excel side of the data. My “Order Details” workbook has a table called AmazonGo with these columns, which I set up to share the Order ID field with the SharePoint list:

To connect it:

1. On the Home tab, select Get data, then choose Excel workbook.
2. Browse to your file and click Open.
3. In the Navigator window, select the table or sheet you need. I’m loading the AmazonGo table here.
4. If the data is already clean, click Load. If not, click Transform Data and repeat the same cleanup steps you used for the SharePoint list.

You now have two tables in your data model — one from SharePoint, one from Excel — both ready for reporting.

Build a Simple Data Model

Before jumping into visuals, take a minute to check how your tables relate to each other. A relationship is a link between two tables based on a shared column, and it’s what lets Power BI combine data correctly across sources. Since both tables in our example share the Order ID column, Power BI usually detects this relationship automatically — but it’s worth confirming in Model view rather than assuming.

For a sales dashboard specifically, I also recommend building a dedicated Date table if you plan to report by month, quarter, or year. It makes time-based filtering far more reliable than relying on whatever date column happens to exist in your source data. This walkthrough on how to create a date table in Power BI covers it step by step, and once you have one, you can set up proper date hierarchies for drilling from year down to day.

Add Visuals to Your Power BI Report

Here’s where the workflow looks different from older tutorials. Power BI Desktop now uses on-object interaction by default, which means there’s no separate Visualizations pane with drag-and-drop wells. Instead, you insert a visual from the ribbon, then build it directly on the canvas.

To add any visual: go to the Insert tab, select Visuals, and choose a chart type from the gallery. Once it’s on the canvas, click Add data on the visual itself (or just drag fields from the Data pane onto it) to populate it.

Add a Card Visual for Total Sales

A card displays a single number — perfect for a headline metric like total sales.

1. Insert a Card visual from the Visuals gallery.
2. Drag Sales into the value field on the card, or click Add data and select it.

If you want this number to update dynamically based on filters, consider building it as a proper measure in Power BI using SUM, rather than just dragging in the raw column. A simple Power BI sum measure like Total Sales = SUM(Sales[Sales]) gives you more control later, especially once you start layering in filters or time intelligence.

Add a Column Chart for Profit by Product

1. Insert a Column chart (specifically a stacked column chart works well here).
2. Drag Product Name to the X-axis.
3. Drag Profit to the Y-axis.

This kind of column chart makes it easy to spot which products are driving — or dragging down — overall profit.

Add a Pie Chart for Sales by Customer

1. Insert a Pie chart.
2. Drag Customer Name into the legend field.
3. Drag Sales into the values field.

A pie chart works best here because we’re showing how a small number of customers split total sales — it gets messy fast with more than six or seven slices.

Add a Slicer to Filter by Region

A slicer is an interactive filter that sits on the report canvas so viewers can filter everything at once.

1. Insert a Slicer visual.
2. Drag Region into the field.

Slicers are one of the most requested features I build for clients, and there’s a lot more you can do with them — from filtering by multiple columns to using a dropdown format instead of a list. It’s worth exploring the full Power BI slicer options once your basic report is working.

Add a Waterfall Chart for Sales by City

1. Insert a Waterfall chart.
2. Drag City into the category field.
3. Drag Sales into the Y-axis.

A waterfall chart shows how each city adds to or subtracts from the total, which is more intuitive than a plain bar chart when you want to tell a running-total story.

Pro Tip: In my experience, clients almost always ask for one more visual after seeing the first draft. Leave extra canvas space or a second page free from the start — rearranging a full page of visuals later takes far longer than planning for growth up front.

Save and Publish the Report

Once your visuals are in place, save your work by selecting File, then Save or Save As, and give the report a clear, descriptive name.

If you want others to view the report without opening Power BI Desktop, you’ll need to publish it to the Power BI Service — Microsoft’s cloud platform for sharing and collaborating on reports. From there, you can pin visuals to a dashboard, set up scheduled refresh so the data stays current, or even bring the report into Microsoft Teams so your team sees it without leaving their usual workspace.

Things to Keep in Mind

• Use the site URL, not the list URL, when connecting to SharePoint. This is the single most common mistake I see — pointing Power BI at the list address instead of the site root causes connection errors every time.
• Plan your list columns before you start entering data. Switching a column’s data type after you’ve got hundreds of rows in it can cause errors or data loss. Get the types right from the start, like in the table above.
• Build measures instead of relying on raw columns for totals. A measure recalculates based on whatever filters are active on the page, while a raw column total doesn’t. This matters the moment you add a slicer.
• Don’t skip the Power Query cleanup step. Blank rows, inconsistent date formats, and mismatched data types cause reports to break silently, often weeks after you built them.
• Watch how many visuals you put on one page. I try to stick to five or six per page. More than that, and the report starts to feel cluttered and loads more slowly.
• Set up scheduled refresh once you publish. A report that never updates is worse than no report at all — people lose trust in it fast.
• Apply Row-Level Security if the data is sensitive. If different regions shouldn’t see each other’s numbers, Row-Level Security restricts what each viewer sees without needing separate reports.

Frequently Asked Questions

You may also like the following tutorials:

• Power BI Dashboard Examples
• How to Add a Filter in Power BI
• How to Create a KPI Visual in Power BI
• How to Change the Data Source in Power BI

Connecting a SharePoint list and an Excel file to Power BI takes just a few minutes, but the real work is in setting up the right columns from the start and choosing visuals that actually answer the questions your team is asking. Start with a simple, well-structured list, then refine your report as people start using it and tell you what they actually need to see. I hope you found this guide helpful.

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.