View Full Version : Import data in powerpivot from another table in powerpivot

2017-06-16, 06:11 PM
I have 3 excel files
2015 has a power pivot table with 3 million rows
2016 has a power pivot table with 9 million rows
2017 has a power pivot table with 5 million rows

All the tables have the same structure (15 columns)

Need to do this

Select * from 2015
union all
Select * from 2016
union all
Select * from 2017

Is this possible? Any suggestion?

Kind Regards, Marky

Dan Bliss
2017-06-17, 05:08 PM
Yes, Power Query can handle files with millions of rows. Even though Excel worksheet tables are limited to 1048576 rows, Power Query can handle much more. The trick to make sure you don't try to load your millions of rows of data into a Excel worksheet.

Because your files are so large, I recommend you change the default settings on Power Query, to prevent a few missteps:
In Excel, under the Data Tab, click the New Query down-down and select Query Options. In Query options window, under Global>Data Load, change the button from "Use standard load settings" to "Specify custom default load settings". Make sure "Load to worksheet" is DESELECTED (reducing unintentional attempts to load million+ rows into Excel worksheets). You may choose "Load to Data Model" if you plan to create Power Pivots using data stored in the Data Model.
(Loading millions of rows of data into the Power Pivot Data Model will work - so long as your computer has sufficient free disk storage for a huge file. If you go this route you'll notice that your excel file will expand to perhaps 1000s of MB in size, and processing will slow.)
While we're in options, make sure under Global>Query Editor that "Display the Formula Bar is checked. Then click OK. (Done with changing options - When you get more familiar with Power Query, you will want to return to Query Options - powerful stuff here.)

I assume your source files are CSV or TEXT files (delimited)? Under the Excel tab >Data>New Query>From File>From CSV or >From Text - to connect to your source files and create a PQ query for each year's data. The Query Editor will pop up showing the first rows of each million-row file. Once you've created 3 such queries, you can append them: In the Query Editor formula bar, type: " = Table.Combine({Table2015, Table2016, Table2017})" (Don't type quotes).
This same functionality is available from the user interface: From Home tab, find New Source>Other Sources>Blank Query. Change the Name property to YearsCombined (or whatever). In the Query Editor formula bar, type: "= Table2015" (no quotes) then hit enter key, creating a reference to Table 2015 query above. Under Home Tab>Combine button drop-down>Append Queries>Click on Three or more tables, highlight from the list of other queries available Table 2016 and Table2017, click add, then OK.

Now that the combined query is created, use the "Close & Load to ... " on the Close & Load" drop down. Make sure in the "Load to" window that in the section, "Select how you want to view this data in your workbook", "Table" is DESELECTED. Again, if you wish to load the data into the Data Model, then click box "Add this data to the Data Model", but knowing that this will expand your Excel file to perhaps 1000s MB in size.

Hope this helps,

2017-06-19, 02:10 PM
"I assume your source files are CSV or TEXT files (delimited)? "

Hi Dan, many thanks for your answer and help, but my source files are power pivot tables, so I need to import those tables to another data model o export that information to an txt or csv file to do what you said!

Regards, Marco

This picture shows what I need


Dan Bliss
2017-06-19, 04:06 PM
Ah! Sorry not to catch the point of your question earlier. So you have huge tables in Power Pivot, and your question is how to do a merge. If each table has a field with unique values - unique across all tables, and you have access to such a table, you could build relationships from the master table of unique values and each table row in PP. But I'm assuming you already know this and you'd don't have available a master table of unique values, and you just want to do a simple merge. Perhaps experts on DAX can work some magic, I'm coming up empty.

My only recommendation is make a new connection to the source data through Power Query, or import again using the Import Wizard, 3 options mentioned here: https://stackoverflow.com/questions/26935373/full-table-join-in-powerpivot

I think it is still the case that data in the PP Data Model is not accessible to Power Query. Microsoft seems to have designed it for data to flow in one direction: PQ => PP, but not PP=> PQ, without using Excel tables (which is impossible for you with millions of rows). Here is a discussion of this limitation on a wish list web page: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9976752-power-query-to-use-a-powerpivot-data-model-as-a-so

Would it be difficult for you to connect to the data source using Power Query? If you go this route, your data model will be duplicated: Table2015, Table2016, Table2017, TableAll3years. So you may want to drop the individual years' tables to save space.

Hope this helps,
Dan Bliss

2017-06-20, 02:44 PM
Dan thank you so much for all the information that you shared with me...

I've been surfing on the internet but there is not the solution to my problem, I can not connect to my data source because this is a forecasting report so it means in this moment information from 2015, 2016 and 2017 early months are sales and not forecast, so the information that I have on the PP tables is my data source and I need to look for a solution to manipulate and merge that information.

The real report appends the information week by week but we think PP can crash soon because it increases about 300.000 rows peer week and PP does not allow to erase rows, so my idea was to separate the information by year and after that merge the information, so it means that we can delete a full year when we consider necessary.

I really appreciate all your tips, your help, and your time!

Regards, Marco

Dan Bliss
2017-06-21, 12:08 AM
Have you researched DAX function: UNION ( table2015, table2016, table2017) (https://msdn.microsoft.com/en-us/library/dn802530.aspx)? (Click on link). UNION does not eliminate duplicate rows when merging tables. There are other DAX set functions, described here (https://msdn.microsoft.com/en-us/library/dn802530.aspx) ithat work in Power Pivot.

2017-06-23, 07:14 PM
Hi Dan,

I have a similar case but I have not got any positive result:

I have two excel files (same structure) and I need to merge the information, after that, I'm trying to calculate ABS but If I use UNION ALL from power pivot or union in DAX I got the same wrong result:

Can you look the following picture and see what I need?


Many thanks for sharing your knowledge.


Dan Bliss
2017-06-26, 09:41 PM

From the Data tab in Excel, click Show Queries in the Get & Transform section of the ribbon.
Over on the right is a list of Workbook Queries. My guess is you have a separate query for each year?

Within that list of Queries, right click on the 2015 table, then choose edit. This brings up the Query Editor in Power Query.
In Excel 2016, on the Home Tab click on the Combine dropdown. Click on the Append Queries, then choose Append Queries as New.
(In other versions of Excel/Power Query, the "Append Queries" choices are elsewhere - search for it.

The "Append Queries as New" launches a window where you can select 2 or more tables to append to one another. Append the 2015 & 2016 table: this will get you the bad result - where 2015 and 2016 data show in separate columns. IMPORTANT: Look at the formula. It should look something like this:

=Table.Combine({Table2015, Table2016})

The reason Append Queries (or actually the Table.Combine() function) fails in your situation is because your table column names are not the same. If you rename each of your column names to the generic Unit_Sales and Amt_Sales, each year's data won't be in separate columns. (Actually, for each table you append, the names and the data-types must be the same.) To retain each year's identity in the data, you must explicitly add a column to each table, such as YEAR and assign it a value such as:

= Table.AddColumn(Source, "Year", each 2015) for the 2015 table, and similarly for other years.

Rather than append tables, you can merge them and get a good result; and you don't need to rename columns.
To merge, click the Combine dropdown, but this time, select Merge Queries, then Merge Queries as New.

In Merge you must select/click the fields that match exactly - in your case, select Product column for each table.
Choose Full Outer as the Join Kind. Click OK
The formula is completely different. Here is the merge function at work:

= Table.NestedJoin(Table2015,{"Product"},Table2016,{"Product"},"NewColumn",JoinKind.FullOuter),
= Table.ExpandTableColumn(Source, "NewColumn", {"2016Unit_Sales", "2016Amt_Sales"}, {"2016Unit_Sales", "2016Amt_Sales"})

This will produce a good result for you.

Hope this helps.

2017-06-30, 05:53 PM
Hello Dan,

All the information you have given me has been of great help and has allowed me to advance a lot in both reports.

In the second case that I mentioned is already solved and working correctly, use a union all of power pivot and then a summarize in Dax Studio.

For the first case that I mention in this publication I have been able to advance a lot and my solution was to separate the whole hierarchy and use it as reference tables, it is working quite well (attached image), but I have an additional doubt, in the final report I use slicers but being related to the data model in power pivot becomes too slow, is there any way to avoid this, disconnect the slicers or something similar?

Many thanks, Marco


2017-07-24, 02:02 AM
This might be of use - https://powerpivotpro.com/2010/07/slicers-and-pivot-update-performance/

2017-07-25, 10:13 PM
Hi folks,

Thanks for your help... The report is almost ready but I have a new question, maybe you can help me again!

I have 3 tables in power pivot and I need to use them as CTE in order to develop a new SQL query! Is it possible?

Look for the attached picture

Thanks, Marky7121