Using Combined Excel data and generate new report

[Sonashish]

Hello

I have multiple Excel Online files with below information, these excel hosted in SharePoint online. Each excel represent to different location like location1, location2, location3.

Excel 1

Then I am pulling data from different location excels and creatin a combine one local summarized excel using formula like IF(Location1!$B$5="N","NA",Location1!$C$5)

Excel 2

Now whenever new event occurred. I need to update both the excel files.  Currently I am manually adding the date and other column in first table for each location in excel online. Then  I am adding another column in table with using IF(Location1!$B$5="N","NA",Location1!$C$5) formula.

I am ok with first excel because I can add multiple excel and combine them. Using the values from combined excel data, I want to generate report as mentioned above in 2nd tables with background color. So whenever if someone add data in first table (Excel online), so 2nd table should update the report with new information.

How this can be done ?

Thanks

Sona

[v-lionel-msft]

Hi @Sonashish ,

Is it like this?

The reason is that the column names of the tables in different Excel files are different.

If the tables have the same field names, they can be merged.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sonashish · Accepted solution]

Hello Lionel,

Actually I found that whatever excel I am combining that sheet tab names are different. Once I renamed the sheet name as Sheet1 in all excel, everything was working as I am expected.

However I did not found that in any bloh or post where it is mentioned that if you combine excel, sheet name should be same.

Thanks

SOna

[v-lionel-msft]

Hi @Sonashish ,

Did you create these two tables on the data source(SharePoint Online)?

You should first import excels from different locations on SharePoint Online to Power BI, then merge the tables (table 1) and create a matrix (table 2) with table1.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sonashish]

I manually combined the excel and able to create the first table and generate pivot table successfully and able to refresh only.

However I am not able to combine the multiple excels from SharePoint online in PowerBI.  After combining the excels, it is always picking first excel only, not sure why? However I am able to combine multiple CSV. I posted same query in forum as well.

[amitchandak]

@Sonashish , do not got  it completely. But Excel 2, need to unpivoted

https://radacad.com/pivot-and-unpivot-with-power-bi