Forum Discussion

Helper II

9 years ago

Solved

concatenate Values repeatly

Hi All, as you can see in the photo, I have a table of two columns. StoreId: every value is repeated three times because every value has three TagId. I'm trying to create a new column which con...

Need Help

v-jiascu-msft

9 years ago

taher

Hi Taher,

If you want it in DAX, you could try these two options.

Option 1: there will be duplicate rows.

New =
CALCULATE (
    CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" ),
    FILTER ( 'Table1', 'Table1'[StoreId] = EARLIER ( Table1[StoreId] ) )
)

Option 2: create a new table.

Table =
SUMMARIZE (
    'Table1',
    'Table1'[StoreId],
    "NewColumn", CONCATENATEX ( 'Table1', 'Table1'[TagId], "-" )
)

Best Regards!

Dale

ImkeF

Community Champion

9 years ago

If you want to concatenate them in 1 column, I'd suggest the following approach:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLCM4yBrOMgCwTOMsUzjJTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StoreID = _t, TagID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StoreID", Int64.Type}, {"TagID", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"StoreID"}, {{"Concatenate", each Text.Combine(_[TagID], "-"), type table}})
in
    #"Grouped Rows"

You group on StoreID, select the TagID column as list and combine all those items (Text.Combine).

File to play around: https://1drv.ms/u/s!Av_aAl3fXRbehasUN2d7tmLElWqypQ