Forum Discussion

Scales's avatar
Scales
New Member
4 years ago
Solved

Merge specific text from multiple columns into new column

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

UserLicence 1Licence 2Licence 3Licence 4
Tony StarkOfficeExcelVisioOutlook
Black WidowOutlook   
Steve RogersExcelVisio  

 

Desired State

UserLicence 1Licence 2Licence 3Licence 4
Tony StarkOfficeExcelVisioOutlook
Black Widow   Outlook
Steve Rogers ExcelVisio 

 

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

Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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"

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.