Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a data imported into BI in the format given in image below:
What I am trying to do is concatenate the values of the description column according to the sequence number column for each particular ID.Also I want to include the carriage return betwen each concatenated value.
Desired output column/measure:
ID Descrition
1001 xyz'Carriage Return'abc
1002 www'Carriage Return'com'Carriage Return'ddy
Can anyone please guide me on how to achieve this.
Thanks,
Akshay
Solved! Go to Solution.
Hi @asharma2 ,
Create a Measure
ABC =
CONCATENATEX (
FILTER (
ALL (
'Table'[Id],
'Table'[seq_no]
),
'Table'[Id]
= MAX ( 'Table'[Id] )
),
CALCULATE (
MAX ( 'Table'[Description] )
),
UNICHAR ( 10 )
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Hi @asharma2 ,
You can do this in Power Query with the following code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUQLhisoqpVgduJARECcmJcOEjKCqysvLkYVAODk/F1nIGIhTUiqVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, seq_no = _t, description = _t]),
groupId = Table.Group(Source, {"id"}, {{"data", each _, type table [id=text, seq_no=text, description=text]}}),
addDescList = Table.AddColumn(groupId, "descList", each Table.Column([data], "description")),
remDataCol = Table.RemoveColumns(addDescList,{"data"}),
extractListValues = Table.TransformColumns(remDataCol, {"descList", each Text.Combine(List.Transform(_, Text.From), "#(cr)#(lf)"), type text})
in
extractListValues
In Power Query, go to New Source > Blank Query, then go to Advanced Editor and paste the code above over the default code in the blank query. You'll then be able to follow the steps I took to do this.
I get the following output:
Pete
Proud to be a Datanaut!
@asharma2 , use https://docs.microsoft.com/en-us/dax/concatenatex-function-dax
and use unichar(10) in the delimiter
concatenatex(Table,table[description],unichar(10) )
Hi Amit,
I want the concatenation to occur per ID and upto the max sequence number for given ID.
Something like below:
Hi @asharma2 ,
Create a Measure
ABC =
CONCATENATEX (
FILTER (
ALL (
'Table'[Id],
'Table'[seq_no]
),
'Table'[Id]
= MAX ( 'Table'[Id] )
),
CALCULATE (
MAX ( 'Table'[Description] )
),
UNICHAR ( 10 )
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |