Forum Discussion

Scales's avatar
Scales
New Member
5 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 a...
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 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"