Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

concatenate value in column

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 Contract...
  • ChrisMendoza's avatar
    3 years ago

    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"