Merge values from multiple rows into one with a separator for each unique list item

[EllieSim]

Hello,

I have a table which lists items (column Code/Descriptor) multiple times depending on the number of corresponding applicable options (column Applicable Schedules). The column Code/Descriptor contains duplicate values, the values in Available Schedules are unique per Code/Descriptor but not unique within the entire column.

Instead, I am trying to achieve the following result:

Code/Descriptor	Code	Code Level	Available Schedules
5.10.1.3.4 Gantries, Trolleys, Blocks and the like	5.10.1.3.4	Level 5	73-04; 84-59; 84-08; 84-13; 84-55; 73-03; 82-07; 84-07; 73-02
5.10.1.3.5 Controls and Electrical Work	5.10.1.3.5	Level 5	44-10
5.10.1.6.2 Complete Hoists Installation	5.10.1.6.2	Level 5	84-11; 84-59

Is there a way to achieve this either in the query editor or with a DAX expression/calculated column, ideally without using the M language?

Many thanks for your help.

Ellie

[v-stephen-msft · Accepted solution]

Hi @EllieSim ,

You can group on Code/Descriptor, Code and Code Level, choose some dummy operation for Available Schedules(e.g. Max, not "All Rows") and then adjust the generated code to combine the names:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdKxCsIwEAbgVzk6tyFpG1tXRVRwFBxKh1APLD0baYLg23vFwXZxsdkukHwJ/5+qirRQUiiRiRz2pvdDiy6G82CJ8MXThmzTOTD9FfwNgdoOo3hyiBcnfCKB5qnIEplHdbysWuaJXodQZRlCVVmQBPTi6thWgFzTRBZB2lpeHRNI56qGrWXV0sfYETZ8R2MILnboppSeUTkXL6fUSqRM3R+EHuFgW+cdHHvnDZHxre2/FO/83fZflFLLvYp/S/0G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code/Descriptor" = _t, Code = _t, #"Code Level" = _t, #"Available Schedules" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code/Descriptor", type text}, {"Code", type text}, {"Code Level", type text}, {"Available Schedules", type text}}),
    #"Grouped Rows" = Table.Group( #"Changed Type" , {"Code/Descriptor","Code","Code Level"}, {{"Available", each Text.Combine([Available Schedules], ";"), type text}})
in
    #"Grouped Rows"

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EllieSim]

Thank you very much.

[Anonymous]

Pivot

https://databear.com/power-bi-pivot-and-unpivot-columns/

[EllieSim]

Hi, thanks for your swift reply. Any thoughts on how to fix the semicolons when merging the pivoted columns? Find and replace still leaves a semicolon at the front and end of each merged result.