Possible to concat an Excel workbook and SharePoint list in one table?

[Daryl_K]

I have a Power BI report that has as one of its data sources a SharePoint list. The sheer volume of items going into this list forces the guy who manages it to do periodic archives, where he takes everything on the list more than 90 days old, copies it to an Excel workbook that is stored in a SharePoint folder, then delete these same items from the "live" list.

I built the report nicely around the list connection, but now want to extend it to look at archived items. As noted the workbook is online, so I have already established a connection to it. Because the sources are the same the columns align, but in order to avoid re-coding hundreds of DAX columns that currently point to the table from the SharePoint list connection I am wondering if it's possible to append the Excel-sourced table to the end. If this works my reports, visuals, etc. should work unchanged.

Is this possible? Keeping in mind both sources can update at any time, can I put two different data sources into one table?

[kcantor · Accepted solution]

@Daryl_K 

It is absolutely possible if the columns match across both tables. Simply pull in the second table and in Query Editor, append the table.  You can disable load on the second table to reduce file size.

Keep in mind that DAX works after the query load. If you append in Query Editor, your DAX would not change. 

If, however, you mean that you want to keep the work you completed in "M" in query editor as opposed to DAX in powerbi, simply move to the top of the applied steps in the query editor and append earlier.

[Daryl_K]

Thanks. I had not messed much with query editors, and to make things worse when he slices large parts of the list out to paste into the Excel "archive" file some columns are renamed and reordered. Here's what I did though:

Established a connection to the Excel Archive file then created a table in Power Query. I went back and forth between the table formed by the original SharePoint list and the one from the Excel file and deleted any unnecessary columns I wasn't using (just to ease confusion), re-ordered and/or renamed columns so that the two had the same number of columns, same headers names, etc. and then finally appended the Archive table onto the "live" SharePoint list table.

This works beautifully because I did not have to change any DAX code. When his SharePoint list gets too large and he moves portions to the Archive Excel workbook it still remains in my one source table, since it is comprised of both sources. And now my report has a historical record too!