Create a column or measure to concatenate records of column based on sequence

[asharma2]

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

[BA_Pete]

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

[amitchandak]

@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) )

[asharma2]

Hi Amit,

I want the concatenation to occur per ID and upto the max sequence number for given ID.

Something like below:

Concatenated = CONCATENATEX(CALCULATETABLE('WO Description','WO Description'[sequence_no]<=MAX('WO Description'[sequence_no]),MAX('WO Description'[work_order])),'WO Description'[short_info],UNICHAR(10))

[harshnathani · Accepted 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!