Combine Multiple Sharepoint Lists to Create One Set of Metrics

[Olijk]

Hello, Is it possible to Combine Multiple Sharepoint Lists to Create One Set of Metrics using Power Automate?  These metrics would be updated daily and placed on a a Sharepoint site. I have seen a lot of articles where this could be done in Power BI, however, we only have access to the Free trial and not the Pro license.  Thank you in advance!

[manuelstgomes]

Hi @Olijk 

Indeed PowerBI is the correct tool to do that, but may I ask what kind of combination you are looking for?

It would be hard to do it in Power Automate, but we can probably provide you with a workaround if we know what you're looking for.

Cheers
Manuel

------------------------------------------------------------------
If I have answered your question, please mark it as "Accept as Solution." It will help other members find the solution faster. If you like my response, please give it a Thumbs Up. ?
Otherwise, reply to it, and the community will do its best to help you.

[Olijk]

Hello,

Could you please explain what is meant by , what combination am I looking for?  

[Olijk]

I would like an Excel table, where I could add pivots and charts that can be updated.  Is this the combination you were referencing in prior post?  I am still kind of new 😀

[manuelstgomes]

Hi @Olijk 

Sorry about that. I wrote my reply a bit too fast and should have answered in a bit more straightforward way.

I asked how you would like to merge the data to get a final result, but I think this would be complex to do in Flow. It's not the intended purpose of the tool to merge data, but this is more suited to PowerBI.

I'm not an expert in PowerBI, but I think you can do some reporting on PowerBI for free but cannot publish without a premium license. If you can get away by running your reports and then exporting them manually, this could be a solution, but please double-check the licensing because I don't want to advise you on something that goes against the rules.

Sorry I could not help more.

Cheers
Manuel

------------------------------------------------------------------
If I have answered your question, please mark it as "Accept as Solution." It will help other members find the solution faster. If you like my response, please give it a Thumbs Up. ?
Otherwise, reply to it, and the community will do its best to help you.

[David-]

It sounds like a job for Power Pivot and Excel: Power Pivot - Overview and Learning (microsoft.com). You can export each of your SharePoint lists to the same Excel workbook and then build your reporting there. Since exporting the data from SharePoint to Excel creates a connection to the list, you would just need to refresh the data in the spreadsheet to update your metrics. 

To use Power Automate to schedule updating the data automatically, @Paulburn has outlined how to do that at Add in Excel Flow the ability to refresh all pivot... - Page 2 - Power Platform Community (microsoft...

[manuelstgomes]

@Olijk you can check @David- solution. Looks good.

Cheers
Manuel

------------------------------------------------------------------
If I have answered your question, please mark it as "Accept as Solution." It will help other members find the solution faster. If you like my response, please give it a Thumbs Up. ?
Otherwise, reply to it, and the community will do its best to help you.

[Olijk]

Hello,

Thank you!  I took a look at this and I have 5 Sharepoint lists that needs to be linked to the Power Pivot.  I created the connection using the ID number of each list. The issue I am having is that these tables almost have the same fields names its just for different group.  When I go to create a Pivot, I have 5 different fields.  I drag and drop fields into the respective columns, filters, etc, but with the duplication column names, its hard to identify which data is for which list.