Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Merge 5 SharePoint lists

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 SharePoin...
v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    BKirsch12
    Icon for Resolver II rankResolver II
    8 years ago

    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.