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 SharePoint lists with Power BI?  Is this even an option with 5 lists?

4 Replies

  • BKirsch12's avatar
    BKirsch12
    Resolver II
    8 years ago

    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:

  • v-piga-msft's avatar
    v-piga-msft
    Resident 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
        Resolver 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.