cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Melmehal
Frequent Visitor

splitting a column that has both a record and a text

Hi everyone, 

 

I have an excel file that came from a JSON code (i think) the data contains a lead ID and has its stage history in sort of a workflow view. the data looks like this: 

 

LEAD IDLatest StageAssigned toLead Created atStage History 
30009Attempted Contact 02/08/2020 18:39[{"from"=>"New", "to"=>"Attempted Contact", "at"=>"2020-02-11 17:33"}]
41736Referred To Workflow 02/04/2020 1:16[{"from"=>"New", "to"=>"Referred To Workflow", "at"=>"2020-02-06 16:30"}]
51309New 02/05/2020 1:25[]
97843LostSherry Ross01/20/2020 17:22[{"from"=>"New", "to"=>"Contacted (In Discussion) ", "at"=>"2020-01-20 18:37"}, {"from"=>"Contacted (In Discussion) ", "to"=>"Quoted ", "at"=>"2020-01-20 22:26"}, {"from"=>"Quoted ", "to"=>"Lost", "at"=>"2020-02-04 22:45"}]
158148Referred or Spam Kendra Warner01/06/2020 15:24[{"from"=>"New", "to"=>"Referred or Spam ", "at"=>"2020-01-06 21:01"}]
177717New 02/16/2020 7:53[]
230944New 01/30/2020 21:26[]
233086Referred or Spam Jessica Yi01/02/2020 16:17[{"from"=>"New", "to"=>"Referred or Spam ", "at"=>"2020-01-02 18:56"}]
233218Referred To Workflow 01/01/2020 21:31[{"from"=>"New", "to"=>"Referred To Workflow", "at"=>"2020-01-02 16:11", "by"=>"Belinda Souster"}, {"from"=>"New", "to"=>"Referred To Workflow", "at"=>"2020-01-02 16:11"}]
233320WonJessica Yi01/02/2020 15:24[]
233397LostSheralea Moon Raymond01/02/2020 18:19[]
233399LostSheralea Moon Raymond01/02/2020 18:20[]
233402LostSheralea Moon Raymond01/02/2020 18:22[{"from"=>"Contacted (In Discussion) ", "to"=>"Won", "at"=>"2020-01-02 19:01"}, {"from"=>"Won", "to"=>"Quoted - Still Interested ", "at"=>"2020-01-03 19:04"}, {"from"=>"Quoted - Still Interested ", "to"=>"Lost", "at"=>"2020-02-18 18:13"}]
233408WonKendra Warner01/02/2020 18:39[{"from"=>"New", "to"=>"Contacted (In Discussion) ", "at"=>"2020-01-06 17:41"}, {"from"=>"Contacted (In Discussion) ", "to"=>"Lost", "at"=>"2020-01-06 23:22"}, {"from"=>"Lost", "to"=>"Quoted - Still Interested ", "at"=>"2020-12-04 17:29", "by"=>"Kendra Warner"}, {"from"=>"Quoted - Still Interested ", "to"=>"Won", "at"=>"2020-12-31 16:42", "by"=>"Kendra Warner"}]

 

 

Now with this stage history column I wanna seperate it so it follows the structure of a relational database. 

 

As an example, for Lead ID 97843 that has this stage history: 

 

[{"from"=>"New", "to"=>"Contacted (In Discussion) ", "at"=>"2020-01-20 18:37"}, {"from"=>"Contacted (In Discussion) ", "to"=>"Quoted ", "at"=>"2020-01-20 22:26"}, {"from"=>"Quoted ", "to"=>"Lost", "at"=>"2020-02-04 22:45"}] 

 

I want to transform this data so that it looks like this: 

 

Lead IDCreated at Stage HistoryAssigned to 
9784301/20/2020 17:22NewSherry Ross
978432020-01-20 18:37Contacted (In Discussion)Sherry Ross
978432020-01-20 22:26QuotedSherry Ross
978432020-01-20 22:26LostSherry Ross

 

 I tried to split the stage history column by deliminater } to give me each stage and its datetime alone but i got this error: 

 

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=
Stage History (UTC).1=[{from=>New, to=>Referred To Workflow, at=>2020-01-02 16:11, by=>Belinda Souster
Type=[Type]

 

 

If anyone knows how I can change the above format into the below format, YOU ARE A GENIUS!!! 

 

Thanks, 

 

Marina

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

Hi @Melmehal ,

 

Believe you need to do a split column in two different splits.

 

First one is by the } and do it to be in rows:

MFelix_0-1610972385200.png

Now do another one by the =>

MFelix_1-1610972420843.png

Now just rearrange the columns that are left replacing text and getting the dates:

Column  - Stage History .3 replace the ", at"  by nothing

Column  - Stage History .3 replace null  by New

Column  - Stage History .4 replace -  by /

Column  - Stage History .4 replace ", by"  by nothing

 

Add a new column for the dates:

if [Stage History .4] = null then [Lead Created at] else [Stage History .4]

 

Remove not needed columns final result below:

 

split_columns.png

 

See full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVVNj9owEP0rVk6tBMJjO3FiqYd+XLZfUmElVBEOKRgVFeJVErRCVf97x0nIB0tCQ3cPHIjGb9578zxeLBxOKQ2ckfM2y/T+IdNr8t7EWbTK8BvBH2UT6k8YZZSAr7gtXfwOnU1i9qHzJjxQynXofNWPoTMioZOZxtcnmEVNlDVqLPKYsjEAAak4D50/S2c5WjgCJPew21RvdJIgxr0hc5P82uzMY4ObKLkp8IZQu4Taw456BDzFacXOBZ7bhugNMu6JDHMtmaI0kL7g+PezSa2ps5/Y+EimJk3tKcAj5SmpGBuioTQVRby6i8mHbbo6pOnWxK9JpxIYl3OUqGREnna6htki8O1gbGV/N8YU8zq6tQBa0NasvnkIiyvcah7g+iD8ZlxMQmYP0d6O5pOO10lE5lES66QwnXql6a5i4qbgnPB71GNqGCgKNUspJcjz2EDJRSqX17FhGDAh2rUw4WVYEJd5zWJOfa9D/keNI1xF5Pu21M5K7Z7KybyMdmZz5nqVdqTIwL9yoZEcVAI5vNiNLvihfihKfhwbJe/0bhuvIzIzhzTTSUd2n5lF7RJnFHXPTdw3ulNs61OBbO+YaKcj8sWYmEyj497E6zMEX0FwhhAMRciZ1giCssEIHRtv0B5Cr654HBS38NIcq8OXVtuYzLLtbkfuYgyCTq9sO8rzTqJ/23ViDlmA4OcT5M3gCOpXwbm08tiNz/hN74x9MaUSXa4Pmm+vF8Wa5Zikjlb16f+cMOTvjn2mgwtbo+X4cySgJ9XIhIPdHIL9AxMMyPIv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"LEAD ID" = _t, #"Latest Stage" = _t, #"Assigned to" = _t, #"Lead Created at" = _t, #"Stage History " = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Stage History ", Splitter.SplitTextByDelimiter("}", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Stage History "),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Stage History ", Splitter.SplitTextByDelimiter("=>", QuoteStyle.Csv), {"Stage History .1", "Stage History .2", "Stage History .3", "Stage History .4", "Stage History .5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Stage History .1", "Stage History .2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",", at","",Replacer.ReplaceText,{"Stage History .3"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"New",Replacer.ReplaceValue,{"Stage History .3"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","-","/",Replacer.ReplaceText,{"Stage History .4"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",", by","",Replacer.ReplaceText,{"Stage History .4"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value3", "Created at", each if [Stage History .4] = null then [Lead Created at] else [Stage History .4]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Created at", type datetimezone}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Stage History .4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Stage History .3", "Status"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Stage History .5"})
in
    #"Removed Columns2"

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User III
Super User III

Hi @Melmehal ,

 

Believe you need to do a split column in two different splits.

 

First one is by the } and do it to be in rows:

MFelix_0-1610972385200.png

Now do another one by the =>

MFelix_1-1610972420843.png

Now just rearrange the columns that are left replacing text and getting the dates:

Column  - Stage History .3 replace the ", at"  by nothing

Column  - Stage History .3 replace null  by New

Column  - Stage History .4 replace -  by /

Column  - Stage History .4 replace ", by"  by nothing

 

Add a new column for the dates:

if [Stage History .4] = null then [Lead Created at] else [Stage History .4]

 

Remove not needed columns final result below:

 

split_columns.png

 

See full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVVNj9owEP0rVk6tBMJjO3FiqYd+XLZfUmElVBEOKRgVFeJVErRCVf97x0nIB0tCQ3cPHIjGb9578zxeLBxOKQ2ckfM2y/T+IdNr8t7EWbTK8BvBH2UT6k8YZZSAr7gtXfwOnU1i9qHzJjxQynXofNWPoTMioZOZxtcnmEVNlDVqLPKYsjEAAak4D50/S2c5WjgCJPew21RvdJIgxr0hc5P82uzMY4ObKLkp8IZQu4Taw456BDzFacXOBZ7bhugNMu6JDHMtmaI0kL7g+PezSa2ps5/Y+EimJk3tKcAj5SmpGBuioTQVRby6i8mHbbo6pOnWxK9JpxIYl3OUqGREnna6htki8O1gbGV/N8YU8zq6tQBa0NasvnkIiyvcah7g+iD8ZlxMQmYP0d6O5pOO10lE5lES66QwnXql6a5i4qbgnPB71GNqGCgKNUspJcjz2EDJRSqX17FhGDAh2rUw4WVYEJd5zWJOfa9D/keNI1xF5Pu21M5K7Z7KybyMdmZz5nqVdqTIwL9yoZEcVAI5vNiNLvihfihKfhwbJe/0bhuvIzIzhzTTSUd2n5lF7RJnFHXPTdw3ulNs61OBbO+YaKcj8sWYmEyj497E6zMEX0FwhhAMRciZ1giCssEIHRtv0B5Cr654HBS38NIcq8OXVtuYzLLtbkfuYgyCTq9sO8rzTqJ/23ViDlmA4OcT5M3gCOpXwbm08tiNz/hN74x9MaUSXa4Pmm+vF8Wa5Zikjlb16f+cMOTvjn2mgwtbo+X4cySgJ9XIhIPdHIL9AxMMyPIv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"LEAD ID" = _t, #"Latest Stage" = _t, #"Assigned to" = _t, #"Lead Created at" = _t, #"Stage History " = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Stage History ", Splitter.SplitTextByDelimiter("}", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Stage History "),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Stage History ", Splitter.SplitTextByDelimiter("=>", QuoteStyle.Csv), {"Stage History .1", "Stage History .2", "Stage History .3", "Stage History .4", "Stage History .5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Stage History .1", "Stage History .2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",", at","",Replacer.ReplaceText,{"Stage History .3"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"New",Replacer.ReplaceValue,{"Stage History .3"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","-","/",Replacer.ReplaceText,{"Stage History .4"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",", by","",Replacer.ReplaceText,{"Stage History .4"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value3", "Created at", each if [Stage History .4] = null then [Lead Created at] else [Stage History .4]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Created at", type datetimezone}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Stage History .4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Stage History .3", "Status"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Stage History .5"})
in
    #"Removed Columns2"

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Melmehal
Frequent Visitor

Thank you sir !! @MFelix 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors