Forum Discussion
Solved
Split and Extract Not Delimited Column with Multiple Values
I have a spreadsheet where a cell contains multiple values in the format below. vti_parserversion:SR|16.0.0.6105
vti_folderitemcount:IR|0
vti_charset:SR|utf-8
vti_author:SR|i:0i.t|00000003...
erikboderek,
Check this code.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBisIwEIZfpXhvSVCL9LSnBQ+CWPRSZYnpxAbaJEwmBaEPb4I9eOju/gNhmMx88zNNsxpJ/ziBHnAE9NqaetA9PLNvDJ5QELRVfZp4WbAYJWfb7GrSjLJ9C6gJBmmDoWp/mtj8JbvEo0VQIJXv5j4RqLO42KYrpgua2FvrPL05U4rnElL2oUk49+XjSnBWG5rZcX9wTlC3iH/09i76a9S4KQbhCTDOnfeXP06wmckPMBCLvxg/aInWW0VZnSwdPyy1dy87GMS/d+Z8XbDV7fYC", 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.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1", Text.Trim, type text}}), #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Cleaned Text", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter1", List.Distinct(#"Split Column by Delimiter1"[Column1.1]), "Column1.1", "Column1.2") in #"Pivoted Column"
Regards,
Lydia
Is there any possibility that you share me the problem data? I will test it in my Desktop.
Regards,
Lydia
Anonymous - Sure. Here's a link to download.
erikboderek,
Check this code.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBisIwEIZfpXhvSVCL9LSnBQ+CWPRSZYnpxAbaJEwmBaEPb4I9eOju/gNhmMx88zNNsxpJ/ziBHnAE9NqaetA9PLNvDJ5QELRVfZp4WbAYJWfb7GrSjLJ9C6gJBmmDoWp/mtj8JbvEo0VQIJXv5j4RqLO42KYrpgua2FvrPL05U4rnElL2oUk49+XjSnBWG5rZcX9wTlC3iH/09i76a9S4KQbhCTDOnfeXP06wmckPMBCLvxg/aInWW0VZnSwdPyy1dy87GMS/d+Z8XbDV7fYC", 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.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}), #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Column1", Text.Trim, type text}}), #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Cleaned Text", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}), #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter1", List.Distinct(#"Split Column by Delimiter1"[Column1.1]), "Column1.1", "Column1.2") in #"Pivoted Column"
Regards,
Lydia