Automatically update PowerBI file from Excel sheet

[Anonymous]

Hi,

I get an excel file every week which has Excel sheet tab names as Week10, Week11, Week12 and so on.

The excel has 2 columns - Name and Points.

I bring in each excel to Power BI every to perform certain analysis. 

A new requirement is that every week when I update the excel sheet in Power BI with a new tab say Week13 and further, it should create a new table (Expected result is show below) with columns -

Name, Week11, Week12, Week 13,.... . 

Below is the sample Source file data and expected output:

Week10 

Name	Points
XYZ	2
ABC	4
XYZ	5

Week11 

Name	Points
XYZ	7
ABC	9
RKO	1
XYZ	6

Week12 tab

Name	Points
XYZ	2
RKO	4
TUV	6
LMN	4

IN my expected result, the new week's column should automatically be added every week and Points summation should be visible against every name:

Expected result:

Name	Week10	Week11	Week12
XYZ	7 (2+5)	13 (7+6)	2
ABC	4	9	0
RKO	0	1	4
TUV	0	0	6
LMN	0	0	4

Similarly when I update the source file with a new tab sat Week13, the data and values should get updated in the "Expected Result" shown above. 

A catch here - Replacing the new week's file should Not wipe off the older weeks data otherwise my report would not show any trend over the weeks.

Any help will be much appreciated.

Thanks!

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous 

As tested, if you have an excel with mant tabs (week10....), then import this excel into Power BI,

each tab is considered as a single table to import into Power BI.

When you add a new tab in excel, to update this into Power BI, you need to connect this tab with Power BI again.

Also, since each tab is a single table in Power BI, we are unable to do a transformation for all tables all the time.

For example, yesterday, i have week10,week11,week12 tables, then i append the three table, 

today, i add a new table week13, i need to re-append these four tables again.

For the reasons above, i would like to suggest you to keep all weeks' data in a excel tab,  then connect this excel with Power BI.

Each time, you add new weeks' data in the excel, once clicking on the "refresh" button in Power BI Desktop, the new data will be updated.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.