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 DataCase NumberField Type123Repair123Repair123Install456Install456Deskside Support987Removal987Install987Repair What I am hoping for...Case NumberField TypeField Type Group123RepairInstall - Repair123RepairInstall - Repair123InstallInstall - Repair456InstallDeskside Support - Install456Deskside SupportDeskside Support - Install987RemovalInstall - Remove - Repair987InstallInstall - Remove - Repair987RepairInstall - Remove - Repair

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 LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 RegardsMaggie Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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).

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?

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

12 Replies

v-juanli-msft
Community Support
7 years ago
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.
- Anonymous
  Not applicable
  7 years ago
  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
    New Member
    6 years ago
    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
- cchp07
  Frequent Visitor
  7 years ago
  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
    Community Support
    7 years ago
    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.

smpa01

Community Champion

7 years ago

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
Not applicable
7 years ago
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).
Anonymous
Not applicable
7 years ago
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
  Community Champion
  7 years ago
  Hello Anonymous
  
  Sorry for the delayed reply. Please post your new dataset. Will work throught it.

v-juanli-msft
Community Support
7 years ago
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

Forum Discussion

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

12 Replies

Related content

concatenate all values for specific value over multiple rows

Concatenate lists ;

concatenate strings

Concatenate multiple columns to a single column

Group and Concatenate

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

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