Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Solved! Go to 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"
= 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"})
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.
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"
= 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"})
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |