Home Forums Power Pivot Concatenate multiple rows of a field into a single column

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

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

    karenlp97
    Participant
    • Started: 8
    • Replies: 24
    • Total: 32

    Hi all,

    If I have the following table:
    <table width=”211″>
    <tbody>
    <tr>
    <td width=”105″>CustomerName</td>
    <td width=”106″>Product</td>
    </tr>
    <tr>
    <td>CustomerA</td>
    <td>Apples</td>
    </tr>
    <tr>
    <td>CustomerA</td>
    <td>Oranges</td>
    </tr>
    <tr>
    <td>CustomerB</td>
    <td>Bananas</td>
    </tr>
    </tbody>
    </table>
    Is it possible to achieve the following desired result:
    <table style=”height: 87px;” width=”247″>
    <tbody>
    <tr>
    <td width=”105″>CustomerName</td>
    <td width=”106″>Products</td>
    </tr>
    <tr>
    <td>CustomerA</td>
    <td>Apples;Oranges</td>
    </tr>
    <tr>
    <td>CustomerB</td>
    <td>Bananas</td>
    </tr>
    </tbody>
    </table>
    Thanks in advance!

    #7650

    karenlp97
    Participant
    • Started: 8
    • Replies: 24
    • Total: 32

    Uploading a screenshot since the tables in my post did not post well…

     

     

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

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    With Excel 2016, the CONCATENATEX function will help you do this. Otherwise, in Power Query, the Text.Combine function can be applied.

    #7666

    karenlp97
    Participant
    • Started: 8
    • Replies: 24
    • Total: 32

    Hi Tom! Nice to hear from you again 🙂

    I am actually working with this problem in Power BI Desktop, but I figured DAX is DAX – I did find the CONCATENATEX function, but it seemed to me like the syntax arguments too two *different* columns to concatenate together. Unless I am missing something?

     

    #7668

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    Karen,

    CONCATENATEX can use one OR more columns as input.

    For example, in the attached pbix file I created a table with one column and three rows, then created the measure

    Measure1 = CONCATENATEX ( Table1, [Column1], “,” )

    The online documentation gives an example with two columns of input, but that is not required as indicated in the formula above.

    Tom

    #7669

    karenlp97
    Participant
    • Started: 8
    • Replies: 24
    • Total: 32

    Hi Tom – thank you – though I think the pbix file missed the boat? 🙂

     

    #7670

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    I will try uploading as a zip.

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

    tomallan
    Keymaster
    • Started: 9
    • Replies: 1931
    • Total: 1940

    Success!

    #7684

    karenlp97
    Participant
    • Started: 8
    • Replies: 24
    • Total: 32

    Hi Tom! Worked like a dream, thank you 🙂

    I had tried unsuccessfully to use it but I think my error was that I was trying to use it as a calculated column and I was running into relationship issues with my tables and had hastily concluded that the function took two arguments to concat two columns.

    At any rate, thank you again, as always!

     

    Karen

    #8203

    aj_gupta_5
    Participant
    • Started: 0
    • Replies: 1
    • Total: 1

    Hi Tom,

     

    Table1
    <table width=”144″>
    <tbody>
    <tr>
    <td width=”72″>Id</td>
    <td width=”72″>Name</td>
    </tr>
    <tr>
    <td width=”72″>1</td>
    <td width=”72″>A</td>
    </tr>
    <tr>
    <td width=”72″>2</td>
    <td width=”72″>B</td>
    </tr>
    <tr>
    <td width=”72″>3</td>
    <td width=”72″>C</td>
    </tr>
    </tbody>
    </table>
     

    Table2
    <table width=”144″>
    <tbody>
    <tr>
    <td width=”72″>Id</td>
    <td width=”72″>Name</td>
    </tr>
    <tr>
    <td width=”72″>1</td>
    <td width=”72″>X</td>
    </tr>
    <tr>
    <td width=”72″>1</td>
    <td width=”72″>Y</td>
    </tr>
    <tr>
    <td width=”72″>1</td>
    <td width=”72″>Z</td>
    </tr>
    <tr>
    <td width=”72″>2</td>
    <td width=”72″>X</td>
    </tr>
    <tr>
    <td width=”72″>2</td>
    <td width=”72″>Y</td>
    </tr>
    <tr>
    <td width=”72″>3</td>
    <td width=”72″>X</td>
    </tr>
    <tr>
    <td width=”72″>3</td>
    <td width=”72″>z</td>
    </tr>
    <tr>
    <td width=”72″></td>
    <td width=”72″></td>
    </tr>
    <tr>
    <td width=”72″></td>
    <td width=”72″></td>
    </tr>
    </tbody>
    </table>
    output looking for
    <table width=”216″>
    <tbody>
    <tr>
    <td width=”72″>Id</td>
    <td width=”72″>NameList</td>
    <td width=”72″>Name</td>
    </tr>
    <tr>
    <td width=”72″>1</td>
    <td width=”72″>X,Y,Z</td>
    <td width=”72″>A</td>
    </tr>
    <tr>
    <td width=”72″>2</td>
    <td width=”72″>X,Y</td>
    <td width=”72″>B</td>
    </tr>
    <tr>
    <td width=”72″>3</td>
    <td width=”72″>X,,Z</td>
    <td width=”72″>C

     

     

     </td>
    </tr>
    </tbody>
    </table>
    How to achive in Power bi using

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

You must be logged in to reply to this topic.