Column Transformation

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.