concatenate value in column

[Anonymous]

oHi,

the subject isn't clear, but let me explain that in an example and it'll be esier:

I've two columns in this way:

Contracts:        Scope of Work:

Contracts 1      Inverter L1,L2

Contracts 1      Inverter L3

Contracts 1      Inverter Supply

Contracts 2      Tracker L1,L2

Contracts 2      Tracker L3

Contracts 2      Tracker Supply

I want trasform it in this way

Contracts         Scope of Work

Contracts 1      Inverter L1,L2+Inverter L3+Inverter Supply

Contracts 2      Tracker L1,L2+Tracker L3+Tracker Supply

So i want concatenate the information in scope of work in one row for every contracts, is possible?

Thank you all.

Flavio

[sondhboyz]

Hi I need this also in new column with Dax measure, please help

[ChrisMendoza · Accepted solution]

@Anonymous - You can do with Power Query 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKylKTC4pVjBU0lHyzCtLLSpJLVLwMdTxMVKK1cGtwBifbHBpQUFOJZoKI6CKECAzG4f5KPLoxiNLwkyPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contracts = _t, #"Scope of Work" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contracts", type text}, {"Scope of Work", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Contracts"}, {{"Grouped", each _, type table [Contracts=nullable text, Scope of Work=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.ToList(Table.SelectColumns([Grouped],"Scope of Work"),Combiner.CombineTextByDelimiter(","))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "+"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Grouped"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","""","",Replacer.ReplaceText,{"Custom"})
in
    #"Replaced Value"

[amitchandak]

@Anonymous ,

refer three methods

Concatenate Text- Measure, DAX Table, and Power Query Table: https://youtu.be/xAh3tz1qo24