Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I need to create a PowerBI report
The datasource is an Excel Spreadsheet
The Excel spreadsheet is a in a central location all deparments can access to update the spreadsheet.
The Excel spreadsheet contains 6 Tabs.
Each tab is for a different department.
Each tab is the same layout and contains the same columns. The data is specific to the department. The tabs are column fields are are:
Tab = Department 1
Date
Issue
Priority Status
Status
Tab = Department 2
Date
Issue
Priority Status
Status
Tab = Department 3
Date
Issue
Priority Status
Status
Tab = Department 4
Date
Issue
Priority Status
Status
Etc
I Import the Excel spreadsheet.
In Model, each tab (deparment) is a query/ table.
There are no relationships with the tables eg
At the end of each month, each department will update their status in the Excel spreadsheet.
The goal is the PowerBi will dynamically & automatically update with the currnet data set.
I will add Total Counts for example for Total Issues, Total count for Status’s of High, Medium, Low, Totals by Deparments and Issues
My question is, in PowerBi, how do I design so there is a relationship between the tables?
Or can I merge all data into one table, and add a new column field ‘Deparment Type’ And how would I do that? eg
1 table with Column fields
Department
Date
Issue
Priority Status
Status
And would this allow the Excel data to dynamically update, so I am not having to edit the PowerBi report each month.
TIA
Solved! Go to Solution.
Hi
yes you can do it in Power query using the function Append.
Just be sure that all tables get same columns names and add on each table a column Dpt with the name of the dpt.
You will have everything in 1 table.
Hi @dd88 ,
Step1: Add custom column "department" for each table.
Add a custom column - Power Query | Microsoft Learn
Step2: Append tables.
Append queries - Power Query | Microsoft Learn
Best Regards,
Jay
Hi @dd88 ,
Step1: Add custom column "department" for each table.
Add a custom column - Power Query | Microsoft Learn
Step2: Append tables.
Append queries - Power Query | Microsoft Learn
Best Regards,
Jay
Thank you all, thats great.
Append workd.
With the Append, I now have 1 table containing all data. I still have the other 6 seperate tables, What do I do with the other 6 tables? delete the tables. I noticed my PowerBi desktop is starting to get slow in performance.
TIA
Hi
yes you can do it in Power query using the function Append.
Just be sure that all tables get same columns names and add on each table a column Dpt with the name of the dpt.
You will have everything in 1 table.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
66 | |
61 | |
46 | |
45 |