Merge specific text from multiple columns into new column

[Scales]

Hello all,

There may be a much smarter way to acheive this, however my scenario is as follows.

I have a list of users which features 4 columns against them and tracks the licences they have applied to their accounts. My issue is that they are scattered between the columns, and I need to sort them into unique columns.

Current State

User	Licence 1	Licence 2	Licence 3	Licence 4
Tony Stark	Office	Excel	Visio	Outlook
Black Widow	Outlook
Steve Rogers	Excel	Visio

Desired State

User	Licence 1	Licence 2	Licence 3	Licence 4
Tony Stark	Office	Excel	Visio	Outlook
Black Widow				Outlook
Steve Rogers		Excel	Visio

The columns were created by splitting a single column by a delimiter, however I cannot seem to find the correct command to arrange these into a helpful format. I've been attempting to search the full range and create a new column where only "Office" is identified, for example. 

I would then repeat for the other 3 licences types.

Many thanks for any input. 

Chris

[Ashish_Mathur · Accepted solution]

Hi,

Another input table you will have to share will be a 2 column table with all software listed in column A and numbers in the second column showing the order in which each software should appear in the result table.

[lbendlin]

Let the data model/DAX do the work for you.  Properly unpivot the data and then you can display it any way you want

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnPq1QILkksylbSUfJPS8tMTgUyXCuSU3OAdFhmcWY+SKK0JCc/P1spVidaySknMTlbITwzJb8cSUZHSQGOQaqCS1LLUhWC8tNTi4qxGAhCsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, #"Licence 1" = _t, #"Licence 2" = _t, #"Licence 3" = _t, #"Licence 4" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"User"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"User", "Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Value] <> "" and [Value] <> " "))
in
    #"Filtered Rows"