Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
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 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.
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]
)
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".
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
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"
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?
Hello @Anonymous
Sorry for the delayed reply. Please post your new dataset. Will work throught it.
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?
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |