locked
Separating comma delimited fields RRS feed

  • Question

  • Hi all,

    I'm new to PowerPivot, and seeking some help on managing separating a field that has comma delimited text. Easiest way would be to demonstrate.

    So in the pic below, Table 1 has a 'Relationship' field that contains the comma separated values "11,22" indicating that it's related to both values in table two... I want define and display this relationship so it's visible as per Table 3...

     

    The data source from Table 1, is a refreshable sharepoint query, the data from Table 2 is refreshable Team Foundation Server (TFS) query, so I have to split the data up in excel rather then re-design the data source.

    Any suggestions?

    Wednesday, November 12, 2014 11:41 PM

Answers

  • I've uploaded a Power Query workbook here. You should be able to copy most of the steps to get what you need from your existing queries.
    Thursday, November 13, 2014 6:11 PM
  • Hi all, 

    I did some more research about this problem and found this link, which describes a process to create a bridge table which I should be able to use to link the tables together. Unfortunately I'm having other problems with PowerPivot, so I haven't yet been able to put this solution to the test. If it doesn't work I'll try the power query solution and report back.

    Thanks for the help.
    Monday, November 17, 2014 10:33 PM

All replies

  • I would look at Power Query to do this work. Power Pivot is a modeling and analysis tool, which, while capable of doing things like this, is not designed for this sort of work. Power Query is a data shaping and transformation tool, which should be able to handle something like this no problem
    • Proposed as answer by Michael Amadi Thursday, November 13, 2014 5:10 PM
    Thursday, November 13, 2014 5:09 PM
  • I've uploaded a Power Query workbook here. You should be able to copy most of the steps to get what you need from your existing queries.
    Thursday, November 13, 2014 6:11 PM
  • In T-SQL delimited string is commonly converted into a single column table (or two columns with ID column). Then it is processed (JOIN-ed) like a table.

    In your case, it is not clear how to achieve the desired goal (using T-SQL it would be a JOIN).

    T-SQL splitting: http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

    SELECT * FROM dbo.fnSplitStringListXML 
    
    ('New York, California, Arizona, Texas, Toronto, Grand Canyon, Yosemite,
    
      Yellow Stone, Niagara Falls, Belgium, Denmark, Hollandia, Sweden', ',')
    
    /* ID StringLiteral
    
    1     New York
    
    2     California
    
    3     Arizona
    
    4     Texas
    
    ....  */




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    • Edited by Kalman Toth Thursday, November 13, 2014 6:32 PM
    Thursday, November 13, 2014 6:29 PM
  • Hi all, 

    I did some more research about this problem and found this link, which describes a process to create a bridge table which I should be able to use to link the tables together. Unfortunately I'm having other problems with PowerPivot, so I haven't yet been able to put this solution to the test. If it doesn't work I'll try the power query solution and report back.

    Thanks for the help.
    Monday, November 17, 2014 10:33 PM
  • Gerrard, the link you've provided seems viable, but I would never suggest it except as a learning opportunity. I knocked out the Power Query solution in < 5 minutes, and I do not use Power Query on a regular basis (lucky enough to have most of my ETL done for me). It will also be much more reusable and automatable. The Power Pivot solution is a manual process that would require some VBA to make it easily repeatable.
    Monday, November 17, 2014 10:47 PM