locked
Using PowerPivot to create tables with multiple text columns RRS feed

  • Question

  • Hi,

    I know that PowerPivot is not designed to create tables that have multiple columns of non-numerical data, however is there a way to user PowerPivot to help produce such tables, possibly indirectly.

    For example I have two different data sets that I am frequently joining; one has all my orders including fields such as product name, product code, product colour and the customerID, the other has all my customers including  profile information such as the country of origin, gender etc.

    From these datasets I want to create numerous variations of the Product table with all of those text fields as columns, but which have been sliced based on customer data.

    Example of such tables may be:

    Product Name, Code and Colour sliced by Female, European Customers.

    or vice versa;

    A customer table include name, email, telephone and address but which has been sliced by the products they've ordered.

    Is there a way to use the PowerPivot model and it's excellent slicing capabilities but to output the tables as described, or do I need to use different software altogether and recreate my PowerPivot data model in that?

    Thanks for any suggestions.



    Friday, June 13, 2014 4:06 PM

Answers

  • You can add a connection to your database inside powerpivot. Using the connection you can use a query to define your table in powerpivot. the query would merge data from your constituent tables and you can apply pivot over it to get it in the format you want. At the end it becomes a new table in powerpivot model which combines all required fields in required format from your database tables

    see below links for more details

    http://javierguillen.wordpress.com/2012/07/30/creating-lookup-tables-in-powerpivot-using-sql-queries/


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Maracles Monday, June 16, 2014 8:33 AM
    Saturday, June 14, 2014 10:12 AM

All replies

  • You really need to have at least one measure for the slicer to function properly, especially if there is a relationship involved.

    There are 2 ways you could get your desired results:

    1. Create measures that actually return the text values as the results.  This can get complicated.

    2. Create a single simple measure to use in your pivot then just hide the column that contains it.  This is the easiest and could be done with a simple count on your fact table.  Something like:

    OrderCount:=
    
    COUNTROWS(Orders)
    Adding this measure to your pivots (then hiding the column if you don't want to see it) should allow the slicers to work on the rest of the text fields.
    • Proposed as answer by Michael Amadi Friday, June 13, 2014 9:42 PM
    • Unproposed as answer by Maracles Saturday, June 14, 2014 8:05 AM
    Friday, June 13, 2014 6:14 PM
    Answerer
  • Thanks Michael,

    I can see what you're saying but I think my question was perhaps poorly written because I'm actually looking for something different - I will try and re-word my original question but perhaps the below clarifies:

    For my work I have to produce numerous tables. A made-up example of which would be something like below:



    The columns in the table above would come from different tables in the PowerPivot model e.g. subject is stored in a subject table that has a relationship with the customer table and likewise for location. As far as I'm aware, PowerPivot doesn't have a way to produce tables that visually look like the above directly as that is not strictly speaking what PowerPivot is designed for.  

    Is there however a way, using either Excel itself, or third party software, a way of taking advantage of my existing PowerPivot model and relationships to create text based tables like the above.

    At the moment I'm creating lots of different tables which have the information for each column, copying and pasting into a new Excel sheet and then using VLOOKUP's against a customer table to produce the above.

    Does this make my aim clearer?
    Saturday, June 14, 2014 8:05 AM
  • If all your other tables come directly from a  database then what you can do is to create a table in powerpivot using a query involving the other tables after applying PIVOT over them to get data in the way you want. you can use query as a source for your table by adding connection to your database.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, June 14, 2014 8:32 AM
  • Hi Visakh, that sounds more like what I'm looking for - I don't fully understand however what exactly is involved. Could you possibly explain in a little more detail how this works. Most of my data does come from databases.

    Thanks.

    Saturday, June 14, 2014 9:25 AM
  • You can add a connection to your database inside powerpivot. Using the connection you can use a query to define your table in powerpivot. the query would merge data from your constituent tables and you can apply pivot over it to get it in the format you want. At the end it becomes a new table in powerpivot model which combines all required fields in required format from your database tables

    see below links for more details

    http://javierguillen.wordpress.com/2012/07/30/creating-lookup-tables-in-powerpivot-using-sql-queries/


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Maracles Monday, June 16, 2014 8:33 AM
    Saturday, June 14, 2014 10:12 AM
  • You can make a pivot table visually look like that by changing the default report layout to tabular. Then instead of the default, compact, expandable pivot layout, all the pivot rows will show up as their own column.
    Saturday, June 14, 2014 12:59 PM
    Answerer