Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
icdns
Post Patron
Post Patron

Column Transformation

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 🙂
1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

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

2.PNG

  • 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"})

1.PNG

  • Extracted Text Before Delimiter

4.PNG

  • Merge the name columns and company name columns separately.

Merge last name columnsMerge last name columnsResultResult

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.

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.

View solution in original post

2 REPLIES 2
v-xuding-msft
Community Support
Community Support

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

2.PNG

  • 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"})

1.PNG

  • Extracted Text Before Delimiter

4.PNG

  • Merge the name columns and company name columns separately.

Merge last name columnsMerge last name columnsResultResult

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.

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.
greenlover
Frequent Visitor


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 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.