Combine Columns - Keeping Reference

[karmaimages]

Good morning,

I extract some information from a SharePoint list which is in the format of the following:

Reference	Name 1	Name 2	Name 3
123	Person 1	Person 2
124	Person 2
125	Person 1	Person 2	Person 3

Is there a way using power query to create a combined column for all "Name" columns removing the blanks so this would look like the following:

Reference	Name
123	Person 1
123	Person 2
124	Person 2
125	Person 1
125	Person 2
125	Person 3

I need the reference to be maintained so creating a new row based on the number of named persons.

Many Thanks

 

[johnny51981]

With the Name 1 through Name 3 highlighted, you'll want to Unpivot those 3 Columns, which will give you 3 columns, your Reference, Attributes, and Value. The Attributes and Value will be the same so feel free to rename one and delete the other, but you'll achieve your desired results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", Int64.Type}, {"Name 1", type text}, {"Name 2", type text}, {"Name 3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Reference"}, "Name","Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"})
in
    #"Removed Columns"

 

[karmaimages]

With the Name 1 through Name 3 highlighted, you'll want to Unpivot those 3 Columns, which will give you 3 columns, your Reference, Attributes, and Value. The Attributes and Value will be the same so feel free to rename one and delete the other, but you'll achieve your desired results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", Int64.Type}, {"Name 1", type text}, {"Name 2", type text}, {"Name 3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Reference"}, "Name","Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"})
in
    #"Removed Columns"

@johnny51981 that was exactly what I was looking for, works great thank you very much!