Home Forums Power Pivot Data Table with Multi-Select Field

This topic contains 9 replies, has 3 voices, and was last updated by  tomallan 5 months, 3 weeks ago.

Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
    Posts
  • #3037

    pvarley
    Participant
    • Started: 2
    • Replies: 5
    • Total: 7

    I’m working with a data table that contains a multi-select field. Each row of data can have multiple values in the column, and the values are separated by semi-colons. Suppose that the column is called Databases, and sample values look like the attachment.

    I want to use the possible values in the field in a slicer and in rows or columns in pivot tables. For example, I would like to have a slicer where I could select “Oracle” to show data only for opportunities that include Oracle. What is the best approach here?

    I thought about creating a lookup table with the possible values in an Excel worksheet and importing it into the data model, but I don’t know how I would relate the data table back to the lookup table because there can be multiple values in the column for a given row.

    I’ve tried creating a measure for each of the possible values using CALCULATE with filter that looks for a specific value like “Oracle,” but I cannot use measures in a slicer. Also, when I put all of the different measures in the Values area of a pivot, I can see the data, but sorting seems very limited compared to what I would get if I had the values in a lookup table.

    Attachments:
    You must be logged in to view attached files.
    #3040

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2308
    • Total: 2317

    Hi,

    From the start you should know that your concept for a multi-value column and a single value slicer runs uphill against good data model design.

    Your model shows signs of a many-to-many relationship between Opportunities and Databases and you need to resolve that upfront.

    How many databases are you going to have?

    Tom

    #3041

    pvarley
    Participant
    • Started: 2
    • Replies: 5
    • Total: 7

    There are about 20 possible values that can appear in this field, and a single opportunity can have as many as 6 or 7.

    #3056

    pvarley
    Participant
    • Started: 2
    • Replies: 5
    • Total: 7

    I’m using Power Query to get the data from Salesforce.com, and so far I’m not doing anything to transform the data in this column. Based on Tom’s comment about the data model, it seems like I should be duplicating each row so that there is only one value in the Database column for each row, as in the attached screen shot. Then I could deal with the many-to-many problem separately.

    Can anyone offer advice on whether Power Query can do this and if so how? So far the only article I can find on the subject seems to deal with the opposite problem. When using Sharepoint as a source, Power Query was splitting the data out on separate rows, but the user wanted the results that I’m getting instead.

    Paul Varley

    Attachments:
    You must be logged in to view attached files.
    #3184

    pvarley
    Participant
    • Started: 2
    • Replies: 5
    • Total: 7

    Ken Puls’ blog post on Separating Values and Text helped me to figure this out. I added a custom column in the Power Query editor with the following formula:

    Text.Split([Database], “;”)

    The resulting column contains a List, and when I click the Expand Column button in the upper right corner of the column name, it duplicates each row for each value in the list. I’m left with a copy of each row with only one value in the new column, which was the goal.

    At first, I wasn’t getting all of the data. I realized this because the first time I loaded the data into the data model, there were fewer rows than there were before expanding the column, which could not be right. It turned out that there were rows in the source data in which the value in the original column was null. The Text.Split function threw an error on the first null and did not go on with the rest of the data. To fix this, I added an earlier step to filter out rows with null in this column. After that, it worked great.

    #3187

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2308
    • Total: 2317

    Hi pvarley,

    Thanks for your update and the excellent insight into the Text.Split() function. I have added that to my “Helps” folder (aka “Tips and Tricks”).

    Sounds like another case of Power Query to the rescue!

    Ken Puls and Miguel Escobar have written an excellent book on Power Query, “M is for (Data) Monkey”. For anyone new to Power Pivot/Power Query who does not have a copy, it is highly (***** out of 5) recommended.

    Am interested in how your model, pivots and slicers are working for you. Since there will be a many-to-many relationship between databases and opportunities, you may find a use for DISTINCTCOUNT() in some of your formulas.

    Tom

    #8963

    soerenamstrup
    Participant
    • Started: 0
    • Replies: 2
    • Total: 2

    Hi @pvarley

    Could you provide us with you model as I do have the same issue.

    / Søren

    #8967

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2308
    • Total: 2317

    Søren,

    In case pvarley is unavailable, could you describe what you think they saw as the issue and how your experience is different?

    It will help others following this post.

    Tom

    #8968

    soerenamstrup
    Participant
    • Started: 0
    • Replies: 2
    • Total: 2

    This is actually a fair reflection.

    I had the exact same issue, but actually managed to handle it with the solution suggested from pvarley.  The situation is as follow.  The different selection-values can be found in column “Spor” in Problemlog.

    The solution was to create Problemlog2 in this PBI-file.

    Attachments:
    You must be logged in to view attached files.
    #8973

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2308
    • Total: 2317

    Søren,

    I am sure that when pvarley returns to this post, they will be glad to hear their idea helped you.

    Tom

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.