Forum Discussion

taher's avatar
taher
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 concatenates every three rows in on row for each single StoreId.

 

I am looking for  "for each" or "while" statements in Dax but till now no luck.

 

Thanks for help I would appreciate any Idea!

 

Regards,

Taher

Need Help
  • v-jiascu-msft's avatar
    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

19 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    taher are you saying you want to concatenate all three tagid into one row and one column?  i.e (1,2,3)

     

    think you need to pivot for that in power query

  • ImkeF's avatar
    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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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

    • taher's avatar
      taher
      Helper II
      9 years ago

      Hi v-jiascu-msft,

       

      sorry, I was on holiday, so I have not tried it until today.

      It did work with Summerize, thanks.

      I would be interested if it could work without adding a new table, I mean is there an alternative option, which enables me doing this concatenating within my base table.

       

      Thank u all :)

      Taher

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        taher

         

        Hi Taher,

         

        Did you try option 1? You don't need to create a new table with this option.

         

        Best Regards!

        Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    taher,

     

    Hi Taher,

     

    Did you solve your problem? Could you please mark the proper answer if it's convenient for you? That will be a help to the others.

     

    Best Regards!
    Dale