Results 1 to 7 of 7

Thread: Import data in powerpivot from another table in powerpivot

  1. #1

    Import data in powerpivot from another table in powerpivot

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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,

  3. #3
    "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

    Click image for larger version. 

Name:	Capture10.PNG 
Views:	10 
Size:	20.1 KB 
ID:	6976

  4. #4
    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:

    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:

    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

  5. #5
    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

  6. #6
    Have you researched DAX function: UNION ( table2015, table2016, table2017)? (Click on link). UNION does not eliminate duplicate rows when merging tables. There are other DAX set functions, described here ithat work in Power Pivot.

  7. #7
    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?

    Click image for larger version. 

Name:	Capture10.PNG 
Views:	9 
Size:	25.7 KB 
ID:	6998

    Many thanks for sharing your knowledge.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts