Concatenate multiple row values into one based on unique ID in specific order

[Anonymous]

Hi,

I have a large set of data and I am trying to create a unique ID based upon two columns but in Alphabetical order.

Current Data

Case Number	Field Type
123	Repair
123	Repair
123	Install
456	Install
456	Deskside Support
987	Removal
987	Install
987	Repair

What I am hoping for...

Case Number	Field Type	Field Type Group
123	Repair	Install - Repair
123	Repair	Install - Repair
123	Install	Install - Repair
456	Install	Deskside Support - Install
456	Deskside Support	Deskside Support - Install
987	Removal	Install - Remove - Repair
987	Install	Install - Remove - Repair
987	Repair	Install - Remove - Repair

[v-juanli-msft]

Hi @Anonymous 

Do you mean you need field type to be sorted as below:

Deskside Support-Install - Remove - Repair for any case number?

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous 

Besides smpa01's solution, you could create a calculated column

Column = 
CONCATENATEX(FILTER(SUMMARIZE(Table1,Table1[Case Number],Table1[Field Type]),[Case Number]=EARLIER(Table1[Case Number])),[Field Type],"-")

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cchp07]

@v-juanli-msft  Is it possible to add a second condition to this formula? I see that you are filtering by Case Number, but could you filter by Case Number and Date. I am working on something similar and got this formula to work for me, but it pulls all of the records instead of just the ones I need for the Case Number and by that Date.  

---

@v-juanli-msft wrote:

Hi @Anonymous 

Besides smpa01's solution, you could create a calculated column

Column = 
CONCATENATEX(FILTER(SUMMARIZE(Table1,Table1[Case Number],Table1[Field Type]),[Case Number]=EARLIER(Table1[Case Number])),[Field Type],"-")

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---

[v-juanli-msft]

Hi @cchp07 

"Is it possible to add a second condition to this formula? I see that you are filtering by Case Number, but could you filter by Case Number and Date. "

Yes, it is possible.

Create a column 

Column =
CONCATENATEX (
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Case Number], 'Table'[date], 'Table'[Field Type] ),
        [Case Number] = EARLIER ( 'Table'[Case Number] )
            && 'Table'[date] = EARLIER ( 'Table'[date] )
    ),
    [Field Type],
    "-",
    [Field Type]
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cchp07]

@v-juanli-msft This worked perfectly. Thanks. 

[Anonymous]

Thank you Maggie. I tried your solution, however, I am still finding that the combinations aren't always in the same format.

Meaning, sometimes they show up as "Install - Repair" and others show up as "Repair - Install".

[DataP]

Where you able to solve this issue? I am having the same problem and I don't know how to fix it.

Any help would be appreciate it. 

DataP

[smpa01]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCkotSMwsUorVwSngmVdckpiTAxYxMTXDKuKSWpxdnJmSqhBcWlCQX1QClrK0MAebl5tflpiDJIKsHaYGYmcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Number" = _t, #"Field Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Number", Int64.Type}, {"Field Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Case Number"}, {{"AD", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
   Source= [AD],
   #"Removed Duplicates" = Table.Distinct(Source),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Field Type", Order.Ascending}})
in
    #"Sorted Rows"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AD"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Case Number", "Field Type"}, {"Case Number", "Field Type"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Case Number"}, {{"AD", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([AD],"IX",1,1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"AD", "Case Number"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Case Number", "Field Type", "IX"}, {"Case Number", "Field Type", "IX"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom1", {{"IX", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom1", {{"IX", type text}}, "en-US")[IX]), "IX", "Field Type"),
    #"Inserted Merged Column" = Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine({[1], [2], [3]}, "-"), type text),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Case Number"},#"Inserted Merged Column",{"Case Number"},"Inserted Merged Column",JoinKind.LeftOuter),
    #"Expanded Inserted Merged Column" = Table.ExpandTableColumn(#"Merged Queries", "Inserted Merged Column", {"Merged"}, {"Field Type Group"})
in
    #"Expanded Inserted Merged Column"

[Anonymous]

Hi,

I am very new to M and Power Query; sorry for the multiple questions.

For the coding you provided; would there be a problem if I had additoinal columns within my data set?

[smpa01]

Hello @Anonymous 

Sorry for the delayed reply. Please post your new dataset. Will work throught it.

[Anonymous]

Hi smpa01,

sorry for the delay. My problem is that my data set changes all the time. Only think I could do is provide sample data (like my last set of data).

Is there certain fields I could change to be more generic based upon whatever the field type changes to?

[Anonymous]

Thank you!

Could you explain how to do it? I am not familiar with how to replicate the code (I have other fields I need to concatenate in a specific order).