locked
Column in Table 1 Value in Table 2 RRS feed

  • Question

  • Hello!

    I have three tables in powerpivot – the two are my reference tables (both have six columns and around fifty rows each) and the third table is my main table with over 400 thousands of rows and nearly 30 columns. The columns of my main table are the row vaules of my reference tables. Is there a way I can link the reference tables to the main table in PowerPivot please?  

    I have all the tables in SQL 2012 server and all the three tables in PowerPivot are linked to SQL server. 

    Thank you for your help



    • Edited by Zeinu Tuesday, December 13, 2016 11:33 AM
    Tuesday, December 13, 2016 10:41 AM

Answers

  • Hi Zeinu,

    If you want link the reference tables, you’d better create a relationship between them. You can use Power Pivot to unpivot your columns to row values, then you can create relationship.

    For example, I have a table shown in the first table, in Query Edit window, click the unpivot column below Transform(highlighted in yellow), you will transform  the column name to row values.





    If this is not what you want, please post the sample data for further analysis.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 14, 2016 3:08 AM
  • Hi Zeinu,

    best solution for you would probably be to unpivot the data at the server already via a view. If that's not possible, you could use a SQL-statement on import to Power Pivot (or Power Query) (see here i.e.: https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/ )

    PowerPivot doesn't like wide tables so you will also improve the performance if you feed your fact-tables in long and slim. 


    Imke Feldmann TheBIccountant.com

    Wednesday, December 14, 2016 2:21 PM
    Answerer
  • Are you using 64bit Office? It is much more memory efficient than 32bit.

    How much memory does your computer have? You want at lease 8g or better. 

    Thursday, December 15, 2016 7:03 PM
  • Hi Zeinu,

    Because your table contains hundreds of thousands of rows, it runs slowly based on 32bit. So you'd better use PowerBI tool to analyse. In PowerBI desktop, you write DAX similar in PowerPivot. When you import data from Datebase, you'd better use Direct Query model which makes you connect the database directly, rather than load it to PowerBI.  For more details about Power Query model, please reviw this article.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, December 20, 2016 2:10 AM

All replies

  • Hi Zeinu,

    If you want link the reference tables, you’d better create a relationship between them. You can use Power Pivot to unpivot your columns to row values, then you can create relationship.

    For example, I have a table shown in the first table, in Query Edit window, click the unpivot column below Transform(highlighted in yellow), you will transform  the column name to row values.





    If this is not what you want, please post the sample data for further analysis.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 14, 2016 3:08 AM
  • Hi Angelia,

    Thank you very much for your reply, it is very helpful. However, because my main table contains hundreds of thousands of rows when I unpivot it in power query, it is unable to process it. It is because of the size of the data that I was looking do it with in powerpivot.  

    Please let me know if there is another way.

    Regards,

    Zeinu 

    Wednesday, December 14, 2016 10:29 AM
  • Hi Zeinu,

    best solution for you would probably be to unpivot the data at the server already via a view. If that's not possible, you could use a SQL-statement on import to Power Pivot (or Power Query) (see here i.e.: https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/ )

    PowerPivot doesn't like wide tables so you will also improve the performance if you feed your fact-tables in long and slim. 


    Imke Feldmann TheBIccountant.com

    Wednesday, December 14, 2016 2:21 PM
    Answerer
  • Thank you very much Imke, that is helpful. I will try that, it doesn't seem it is possible to do it within PowerPivot. 

    Best,

    Zeinu

    Thursday, December 15, 2016 4:57 PM
  • Are you using 64bit Office? It is much more memory efficient than 32bit.

    How much memory does your computer have? You want at lease 8g or better. 

    Thursday, December 15, 2016 7:03 PM
  • Hi Ed Price,

    I am using 32bit and have 4g 

    Thanks, Zeinu

    Friday, December 16, 2016 8:38 AM
  • Hi Zeinu,

    Because your table contains hundreds of thousands of rows, it runs slowly based on 32bit. So you'd better use PowerBI tool to analyse. In PowerBI desktop, you write DAX similar in PowerPivot. When you import data from Datebase, you'd better use Direct Query model which makes you connect the database directly, rather than load it to PowerBI.  For more details about Power Query model, please reviw this article.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, December 20, 2016 2:10 AM