Merge 5 SharePoint lists

[Anonymous]

Need is to merge the data from 5 SharePoint lists and produce 1 chart/report.

Report Builder 3.0 is past option with just 2 lists, but 5 is causing more of a challenge.

Anyone merged 5 SharePoint lists with Power BI?  Is this even an option with 5 lists?

[v-piga-msft]

Hi @Anonymous,

By my tests, it seems that there is no good way for us to merge more than 2 queries at one time currently in Power Query.

You could try merge 2 queries at first then merge the third query to the new table, and merge as this. However, if you're in this way, it will have a bad  impact on performance.

Besides, You could vote this similar ideas here which has been submitted in Power BI ideas forum and add your comments there to improve Power BI and make this feature coming sooner.

Best Regards,

Cherry

[Anonymous]

what about append instead of merge?  That option is available for 3 or more tables.

Only issue I'm running into is the ID column we use and Power BI doesn't like the dup values.

I'm trying to create a new column which is just the ID + string value for each list, but can't get a number + string to join.

There's gotta be something I can do to convert the number to a string so the concat will work.

[BKirsch12]

I should have specified earlier.

Append is essentially adding rows to the data set. It requires all datasets to have the same exact column headings and data types, then pieces them together

Merge is how PowerQuery handles joins between tables.

This article does a good job explaning it. http://radacad.com/append-vs-merge-in-power-bi-and-power-query

The question is, if you have 5 sharepoint lists that are the same, then you can append them all. If they don't contain the same information, then you will have to merge them using the proper joining mechanisms.

[BKirsch12]

Absolutely, assuming they are using the same data structure, you can use the append queries part of PowerQuery and then choose the Three or more tables option. Let me know if you have any other questions. Example below: