digestVidSign in
summary

excelisfun - Excel Magic Trick 1332: Power BI Desktop: Import Multiple Excel Files & Build Dashboard

Thumbnail of the video “excelisfun - Excel Magic Trick 1332: Power BI Desktop: Import Multiple Excel Files & Build Dashboard”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Technology Business
Tags: technology, business
Views: 2 · Last viewed: 2026-10-16 00:54 UTC

Short Summary

Main recommendation: Use Power BI Desktop's Power Query, rather than a separate Excel setup, to import and consolidate many Excel files from one folder into a single table, then build interactive visualizations on top of it. The video frames this as a free Microsoft tool that reuses Excel Power Query skills, and it demonstrates a dashboard whose charts filter together when a city or sales rep is clicked. The new file-drop test shows that adding a workbook to the folder and refreshing updates the dashboard.

Key Points

  • Power BI Desktop combines Power Query, Power Pivot, Power View, and visualization tools in one free download from Microsoft, so it does not require Excel.
  • Power Query skills from the previous video (importing and transforming multiple Excel files) transfer directly to Power BI Desktop.
  • Each city workbook holds date, product, units, and sales on sheets named by sales rep, so the query must extract the sheet name as the rep and the file name as the city.
  • Only the Power Query and visualization features are used in this example; no DAX formulas are written.
  • Dropping a new file into the source folder and clicking Home > Refresh adds its data to the dashboard automatically.

Details Worth Noting

  • Concrete instructions:
    • Download the course folder from the link below the video, unzip it, and download Power BI Desktop from Microsoft (a free download).
    • Save the new file with F12 (Save As) and name it, e.g., "1332 Boomerang Incorporated 2016 to 17 dashboard."
    • Choose Get Data > More > Folder, browse to the folder of Excel files, click OK, then Edit (or Transform Data).
    • Right-click the Name column, choose Transform > lowercase, then filter the Name column text to equal "xlsx" or "xlsm" (using "Or") so only those files remain.
    • Split the Name column by delimiter "." using the rightmost delimiter option to get the city name from the file name.
    • Keep only the Name and Content columns (Remove Other Columns), then add a custom column using the formula Excel.Workbook([Content], true) to read the workbook objects and promote headers.
    • Remove the Content column, expand the new column (uncheck Hidden, keep Kind, Data, and Name), and filter Kind to equal "Sheet" and Name to not begin with "Sheet" to remove unnamed sheets.
    • Expand the Data column without original column names, rename the columns to City, Sales Rep, Date, Product, Units, Sales, and set types (Date as date, Units as whole number, Sales as decimal number, others as text).
    • Name the query (e.g., "All City Tables") and click Close & Apply.
    • Build visuals: a map with City and Sales; two bar charts (City on the axis with Sales as values and Sales Rep as legend; Product on the axis with Sales as values); add a centered title text box.
    • To add data later, copy the new Excel file into the source folder and click Home > Refresh.
  • The video notes that Power BI Desktop must be re-downloaded manually to update; there is no in-app update button like Excel's.
  • Filtering in the query is done by exact text match, and case matters, which is why the lowercase transform is applied first.

Bottom Line

Power BI Desktop can consolidate a folder of similar Excel workbooks into one table and turn it into a linked, interactive dashboard, using Power Query steps the viewer already knows from Excel.