Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |