How can I achieve this

[phillipK]

Currently the data is this. There are a other columns to the right but I need it to show in a PowerBI Table visual side by side below. Management wants to see in a Row format (some IDs there are 3 employees involved while some has only one). The data when imported from a sharepoint online list is the one shown below. I tried to Unpivot other columns but somehow it is not working right. What is the best approach? Is there a way to put the data side by side in a row as some project ID has several members involved and they want to know

ID	Owner 1	Owner2	Owner 3
111	Daniel C	Eric W	Chris Y

In the Powerquery editor it like like this.

 

[johnny51981]

Hi @phillipK

Give this a try. You will need to augment to fit your table and column header names, but this should give you what you are looking for.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name1", type text}, {"Name2", type text}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Owners", each Text.Combine([Name1],"//"), type nullable text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Owners", Splitter.SplitTextByDelimiter("//", QuoteStyle.Csv), {"Owner 1", "Owner 2", "Owners 3"})
in
    #"Split Column by Delimiter"