Help with Model design from datasource Excel Spreadsheet with multiple tabs with same column layout

[dd88]

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

[Anonymous · Accepted 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.

[Anonymous · Accepted solution]

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

[dd88]

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

[Anonymous · Accepted 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.