locked
Concatenating lists within a table RRS feed

  • Question

  • I have a in power query setup like

    Project Name | Team A | Team B | Team C...

    Project  1      | list         | list       | list...   

    Project  2      | list         | list       | list...   

    ...and I'd like to expand the lists within this one table, using something like the Text.Combine function.

    this table was created by taking a table that has columns named ProjectName, ResourceName, TeamName_R and pivoting it on ResourceName.

    The advanced code looks like

    let
        Source = OData.Feed("https://zyx.sharepoint.com/sites/pwa/_api/ProjectData/Assignments?$select=ProjectName,ResourceName,TeamName_R"),
        #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[TeamName_R]), "TeamName_R", "ResourceName", List.Distinct)
    in
        #"Pivoted Column"

    But I'm having a zero luck with all my attempts at making it a table that reads like

    Project Name | Team A | Team B | Team C...

    Project  1      | staffa,staffb | null | staffz...   

    Project  2      | staffa | staffg,staffh| null...   

    Any pointers would be appreciated.


    Ian


    Wednesday, January 4, 2017 9:23 PM

Answers

  • ok, I did it! Changing the aggregation function from List.Distinct to

    each Text.Combine(_,",")
    ... I'm always amused that no matter how long I stare at something and try different routes, If I ask for help, many many times I'll solve it myself moments later. Hope this helps someone else!

    Final code:

    let
        Source = OData.Feed("https://xyz.sharepoint.com/sites/pwa/_api/ProjectData/Assignments?$select=ProjectName,ResourceName,TeamName_R"),
        #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[TeamName_R]), "TeamName_R", "ResourceName", each Text.Combine(_,","))
    in
        #"Pivoted Column"

    Ian
    • Edited by Ian Bruckner Wednesday, January 4, 2017 9:48 PM
    • Marked as answer by Ian Bruckner Wednesday, January 4, 2017 9:48 PM
    Wednesday, January 4, 2017 9:46 PM