excelpowerbipowerquerym
Remove duplicate texts in one cell
I am currently shaping my data. I have one column called "Centro". However, there are so many duplicate texts in just one cell. How Can I remove the duplicate texts and only show distinct texts? Anyone can help on this?Thanks!!
This is my code:
let
Source = Etapa_2_Caricam,
#"Grouped Rows" = Table.Group(Source, {"Material"}, {{"mynewtable", each _, type table [Material=number, Num Form=text, Created on=date, FechaCreac=date, Initiator=text, Texto tarea=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "NoForm", each Table.Column([mynewtable],"Num Form")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"NoForm", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Added Custom1" = Table.AddColumn(#"Extracted Values", "Iniciador", each Table.Column([mynewtable],"Initiator")),
#"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Iniciador", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Values1", {{"Iniciador", each Text.BeforeDelimiter(_, ", "), type text}}),
#"Added Custom2" = Table.AddColumn(#"Extracted Text Before Delimiter", "FechaInicio", each Table.Column([mynewtable],"Created on")),
#"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"FechaInicio", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Values2", {{"FechaInicio", each Text.BeforeDelimiter(_, ", "), type text}}),
#"Added Custom3" = Table.AddColumn(#"Extracted Text Before Delimiter1", "FechaFinalTarea", each let dates = Table.Column([mynewtable],"FechaCreac") in [min = List.Min(dates), max = List.Max(dates)]),
expanded = Table.ExpandRecordColumn(#"Added Custom3", "FechaFinalTarea", {"min", "max"}),
#"Changed Type" = Table.TransformColumnTypes(expanded,{{"min", type date}, {"max", type date}, {"FechaInicio", type date}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type", "TextoTarea", each Table.Column([mynewtable],"Texto tarea")),
#"Extracted Values3" = Table.TransformColumns(#"Added Custom4", {"TextoTarea", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Changed Type1" = Table.TransformColumnTypes(#"Extracted Values3",{{"Material", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"FechaInicio", type text}}, "en-US"), "FechaInicio", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"FechaInicio.1", "FechaInicio.2", "FechaInicio.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FechaInicio.1", Int64.Type}, {"FechaInicio.2", Int64.Type}, {"FechaInicio.3", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"FechaInicio.2", "FID"}, {"FechaInicio.1", "FIM"}, {"FechaInicio.3", "FIA"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"FIM", type text}, {"FID", type text}, {"FIA", type text}}),
#"Added Custom5" = Table.AddColumn(#"Changed Type3", "FechaInicio", each [FID]&"/"&[FIM]&"/"&[FIA]),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom5",{{"FechaInicio", type date}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type4",{"FIM", "FID", "FIA"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Material", "mynewtable", "NoForm", "Iniciador", "FechaInicio", "min", "max", "TextoTarea"}),
#"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Reordered Columns", {{"max", type text}}, "en-US"), "max", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"max.1", "max.2", "max.3"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"max.1", type text}, {"max.2", type text}, {"max.3", type text}}),
#"Added Custom6" = Table.AddColumn(#"Changed Type5", "FechaFinal", each [max.2]&"/"&[max.1]&"/"&[max.3]),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Material", "mynewtable", "NoForm", "Iniciador", "FechaInicio", "FechaFinal", "min", "max.1", "max.2", "max.3", "TextoTarea"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Reordered Columns1",{{"FechaFinal", type date}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type6",{"max.1", "max.2", "max.3"}),
#"Changed Type7" = Table.TransformColumnTypes(#"Removed Columns2",{{"FechaFinal", type text}, {"FechaInicio", type text}}),
#"Added Custom7" = Table.AddColumn(#"Changed Type7", "Table", each Table.Column([mynewtable],"NoMatAnt")),
#"Extracted Values4" = Table.TransformColumns(#"Added Custom7", {"Table", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Extracted Values4", "Text Before Delimiter", each Text.BeforeDelimiter([Table], ", "), type text),
#"Removed Columns3" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Text Before Delimiter"}),
#"Extracted Text Before Delimiter2" = Table.TransformColumns(#"Removed Columns3", {{"Table", each Text.BeforeDelimiter(_, ", "), type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Extracted Text Before Delimiter2", "Table", "Table - Copy"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","CR","",Replacer.ReplaceText,{"Table - Copy"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","DO","",Replacer.ReplaceText,{"Table - Copy"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","GT","",Replacer.ReplaceText,{"Table - Copy"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","PR","",Replacer.ReplaceText,{"Table - Copy"}),
#"Renamed Columns1" = Table.RenameColumns(#"Replaced Value3",{{"Table - Copy", "No.FormAnt"}, {"Table", "No.MatAnt"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns1",{{"TextoTarea", Text.Trim, type text}}),
#"Added Custom8" = Table.AddColumn(#"Trimmed Text", "Centro", each Table.Column([mynewtable],"Ce")),
#"Extracted Values5" = Table.TransformColumns(#"Added Custom8", {"Centro", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
#"Extracted Values5"
Solution
The easiest way to do this would be to split the comma separated values (Centro field), into different rows and then remove duplicates. Then you can group them up again to get the comma separated values. For the purpose of demonstrating, I created a table with two fields: PrimaryKey and Centro. Then I used the following steps to get to the desired output:
Source Information:
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrUSdJJUorViVYyAvKSdVJ0knWSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)),
let _t = ((type text) meta [Serialized.Text = true]) in type table [PrimaryKey = _t, Centro = _t])
Split Column By delimiter (You can do this by selecting the "Split Column - By Delimiter" option in the Transform tab, you have to choose the Centro field before selecting this option):
= Table.ExpandListColumn(Table.TransformColumns(Source, {{"Centro", Splitter.SplitTextByDelimiter(",")}}), "Centro")
Remove Duplicates (You can do this by choosing "Remove Rows - Remove Duplicates" from the Home tab, make sure you choose all the columns before selecting this option):
= Table.Distinct(#"Split Column by Delimiter")
Grouped Rows (You can do this by choosing the "Group By" option in the transform tab, but you would have to edit the query a little to use the delimiter aggregation):
= Table.Group(#"Removed Duplicates", {"PrimaryKey"}, {{"Centro_New", each Text.Combine([Centro],","), type text}})
This should give you the desired output. Hope this helps.
Edit: You can combine all these into a single step and use the following formula:
= Table.Group(Table.Distinct(Table.ExpandListColumn(Table.TransformColumns(Source, {{"Centro", Splitter.SplitTextByDelimiter(",")}}), "Centro")),{"PrimaryKey"}, {{"Centro_New", each Text.Combine([Centro],","), type text}})
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port