Create a main table from other tables with links & data updates automatically

[dd88]

The data source is an Excel spreadsheet, with tabs. Each tab is for different departments in the organisation. The tabs have the same columns/fields. Users go into and work in their department tab and update data. Total of 11 tabs. in the future there maybe more tabs if new departments/teams created.

In power bi each tab is a unique table.

I would like to create a new Main table that contains all the tables. if tab data is updated, the main table to link and automatically update data from the tab table. The main table will be used for me to work in, do calculations & create a summary dashboard displaying visuals that contains all items.

How can this be achieved?

--- updated ---

I think i worked it out.

As a test 

in Home tab, select Transform Data, Transform Data, on Home, select Combine, Append Queries, Append Queries as New. select 2 tables. Save and close

In the Excel spreadsheet, update data in the 2 tabs. close

In Power bi, performed a refresh. updated data displays in the orginial tab table and in the main table

TIA

[dd88]

Thank you @Ritaf1983  thats great ..  

[Ritaf1983]

Happy to help 🙂

please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hi @dd88 

If you don't want the 2 of tables, but 1 combined you need "Append queries" instead of Append Queries as New..
+

Please refer to the linked video which explains how to do it with dynamic Excel.

https://www.youtube.com/watch?v=gQo5coD9c4M

If previous post helped, then please consider Accepting it as the solution to help the other members find it more quickly