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.
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.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |