Column Transformation

[icdns]

Hello everyone, I would like to ask your help on this data transformation. I have this "CUSTOMER_TABLE" which contains a list of customers. But you can see that it has 2 formats: Example: 1st Format - LUNA, JUAN MIGUEL COMPANYABC (LAST NAME, FIRST NAME COMPANY NAME) 2nd Format - DOMINIC REYES (FIRST NAME, LAST NAME) CUSTOMER_TABLE LUNA, JUAN MIGUEL COMPANYABC PARK, PATRICK KEITH COMPANYABC MENDOZA, DAVE ANTONY COMPANYABC DOMINIC REYES RAMIREZ SUE Would appreciate if you can help me in converting 2nd Format to 1st Format. And Remove the "Company Name (COMPANYABC)" from 1st Format and display it as another column 🙂 The company name is the same across the data. Hope you could really help me on this!! Thank you in advance 🙂

[v-xuding-msft · Accepted solution]

Hi @icdns ,

I created a  sample that implement the requirement by splitting columns and then merge them. You could follow the steps to have a try.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYxBDgMhDMS+EnHmE1OI2pRNQFmoxCL+/41We+zVtrxWOIYh0nvASOU5+KBUtcEmHinsuEKDl0gN3SUVKiz99Z8oW67Xb5PxYYL1avMWuaqYJHKefN7EoeJ80Tk47P0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Last name", each if Text.Contains([Column1.1], ",") then [Column1.1] else [Column1.2]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "First name", each if [Last name] = [Column1.2] then [Column1.1] else [Column1.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1.1", "Column1.2"}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Columns", "Text Before Delimiter", each Text.BeforeDelimiter([Last name], ","), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Last name"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"First name", "Text Before Delimiter", "Column1.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Name"}, {"Column1.4", "Company Name"}})
in
    #"Renamed Columns"

• Split Column by Delimiter

• Add new columns and remove the columns 

= Table.AddColumn(#"Split Column by Delimiter", "Last name", each if Text.Contains([Column1.1], ",") then [Column1.1] else [Column1.2])

= Table.AddColumn(#"Added Custom", "First name", each if [Last name] = [Column1.2] then [Column1.1] else [Column1.2])

= Table.RemoveColumns(#"Added Custom1",{"Column1.1", "Column1.2"})

• Extracted Text Before Delimiter

• Merge the name columns and company name columns separately.

Merge last name columnsResult

I attach my sample that you can download.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

[greenlover]

Hello everyone,

I would like to ask your help on this data transformation. I have this "CUSTOMER_TABLE" which contains a list of customers.

But you can see that it has 2 formats:

Example: 1st Format - LUNA, JUAN MIGUEL COMPANYABC (LAST NAME, FIRST NAME COMPANY NAME)

2nd Format - DOMINIC REYES (FIRST NAME, LAST NAME)

CUSTOMER_TABLE

LUNA, JUAN MIGUEL COMPANYABC
PARK, PATRICK KEITH COMPANYABC
MENDOZA, DAVE ANTONY
DOMINIC REYES RAMIREZ SUE

Would appreciate if you can help me in converting 2nd Format to 1st Format. And Remove the "Company Name (COMPANYABC)" from 1st Format and display it as another column 🙂

The company name is the same across the data. Hope you could really help me on this!! Thank you in advance 🙂