Extract certain information from a cell

[jeroenwmwillems]

Hi guys,

I have a bit of a difficult situation:

I have a column that varies in order of text per row and am trying to find a way to extract a particular part of the information. 

In one case, the text in the cell looks like this:

{"reservationName":"Jodie","reservationPhone":"0123456789","partySize":"Party of 4","arrivalTime":"4:02 PM"}

In another cell, the information can be set up like this:

{"partySize":"Party of 4","arrivalTime":"4:02 PM","reservationName":"Jodie","reservationPhone":"0466836674"}

The order varies per row. 

I am trying to get a column, only stating the time (i.e. 4:02 PM). In excel, I used a function to find the arrival time:

=RIGHT(A2,LEN(A2)-FIND("arrivalTime",A2)) and afterwards cut off the text around it.

I can't get the trick done in PowerBI while modeling the data. What's the best way to get this done?

Cheers,

Jeroen

[v-xicai · Accepted solution]

Hi @jeroenwmwillems ,

Please download my pbix to see more details.

let
   Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RKkotTi0qSyzJzM/zS8xNjVGyilHyyk/JBLJ0UGQDMvLzINIGhkbGJqZm5haWYDUFiUUllcGZVRDJABBPIT9NwQQsmVhUlFmWmBOSCTXaxMrASCHAN0apVilWB+wA8rTrkOdyEzMzC2MzM3MTsANiAQ==", 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.None), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
   #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}}),
   #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","{"," ",Replacer.ReplaceText,{"Column1.1"}),
   #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}"," ",Replacer.ReplaceText,{"Column1.4"}),
   #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {}, "Attribute", "Value"),
   #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if Text.Contains([Value], """arrivalTime""") then 1 else null),
   #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
   #"Replaced Value2" = Table.ReplaceValue(#"Filtered Rows",""""," ",Replacer.ReplaceText,{"Value"}),
   #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2"," : ","_",Replacer.ReplaceText,{"Value"}),
   #"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value3", "Value", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Value.1", "Value.2"}),
   #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute", "Value.1", "Custom"}),
   #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value.2", "arrivalTime"}})
in
   #"Renamed Columns"

You can download my test pbix here: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ebd4RPR7cr5OsAArMZ... .

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.