splitting a column that has both a record and a text

[Melmehal]

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 ID	Latest Stage	Assigned to	Lead Created at	Stage History
30009	Attempted Contact		02/08/2020 18:39	[{"from"=>"New", "to"=>"Attempted Contact", "at"=>"2020-02-11 17:33"}]
41736	Referred To Workflow		02/04/2020 1:16	[{"from"=>"New", "to"=>"Referred To Workflow", "at"=>"2020-02-06 16:30"}]
51309	New		02/05/2020 1:25	[]
97843	Lost	Sherry Ross	01/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"}]
158148	Referred or Spam	Kendra Warner	01/06/2020 15:24	[{"from"=>"New", "to"=>"Referred or Spam ", "at"=>"2020-01-06 21:01"}]
177717	New		02/16/2020 7:53	[]
230944	New		01/30/2020 21:26	[]
233086	Referred or Spam	Jessica Yi	01/02/2020 16:17	[{"from"=>"New", "to"=>"Referred or Spam ", "at"=>"2020-01-02 18:56"}]
233218	Referred 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"}]
233320	Won	Jessica Yi	01/02/2020 15:24	[]
233397	Lost	Sheralea Moon Raymond	01/02/2020 18:19	[]
233399	Lost	Sheralea Moon Raymond	01/02/2020 18:20	[]
233402	Lost	Sheralea Moon Raymond	01/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"}]
233408	Won	Kendra Warner	01/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 ID	Created at	Stage History	Assigned to
97843	01/20/2020 17:22	New	Sherry Ross
97843	2020-01-20 18:37	Contacted (In Discussion)	Sherry Ross
97843	2020-01-20 22:26	Quoted	Sherry Ross
97843	2020-01-20 22:26	Lost	Sherry 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

[MFelix · Accepted solution]

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:

Now do another one by the =>

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:

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"

[Melmehal]

Thank you sir !! @MFelix