cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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 NumberField Type
123Repair
123Repair
123Install
456Install
456Deskside Support
987Removal
987Install
987Repair

 

 

What I am hoping for...

Case NumberField TypeField Type Group
123RepairInstall - Repair
123RepairInstall - Repair
123InstallInstall - Repair
456InstallDeskside Support - Install
456Deskside SupportDeskside Support - Install
987RemovalInstall - Remove - Repair
987InstallInstall - Remove - Repair
987RepairInstall - Remove - Repair

 

11 REPLIES 11
Super User I
Super User I

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

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"

sn1.JPG

Anonymous
Not applicable

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

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

Re: Concatenate multiple row values into one based on unique ID in 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?

Super User I
Super User I

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

Hello @Anonymous 

 

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

 

Community Support
Community Support

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

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],"-")

5.png

 

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

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

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

Anonymous
Not applicable

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

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?

Community Support
Community Support

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

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

 

cchp07
Frequent Visitor

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

@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],"-")

5.png

 

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.


 

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors