Extract a value from text that follows a specific word / identifier

[leadmarlin]

Hi,

I'm trying to create a calculated column that extracts the country from a JSON string.

I don't have or want to create a Country table to select the country.

The source text is:

"{""FirstName"":""Myname"",""LastName"":""Mylastname"",""Email"":""not@not.com"",""Phone"":""1234455"",""Country"":""United States of America"",""Company"":""Global Trade"",""Title"":""Manager""}"

So in this case I want to extract 'United States of America' from the string. 

I tried various options but it exits with the error "The search Text provided to function 'SEARCH' could not be found in the given text"

E.g.

ExtractedValue =

VAR TextToSearch = Combined[Content.CustomerInfo]

VAR FirstColonPosition = SEARCH(""":""", TextToSearch, 1) // Find the first colon

VAR SecondColonPosition = SEARCH(""":""", TextToSearch, FirstColonPosition + 1) // Find the second colon

VAR StartPosition = SecondColonPosition + 3 // Move the start position to the second colon

VAR EndPosition = SEARCH("""", TextToSearch, StartPosition + 1) // Find the closing double quote after the second colon

RETURN

    MID(TextToSearch, StartPosition, EndPosition - StartPosition) // Extract the string between the second colon and the closing double quote

Any hints?

[leadmarlin · Accepted solution]

Hi Dhairya,

Unfortunately the JSON is stored in an Azure Table Column, hence my approach.

I didn't see a way to pivot or split the column other than by delimiter which does not work very well.

BUT...your answer pointed me in the right direction.

I used "Transform / Parse as JSON" which turns the column into cells with records.

After this I created a new custom column that retrieves the records from the column.

E.g. for firstName I use: 

= Table.AddColumn(#"Parsed JSON", "FirstName", each Record.Field([Content.CustomerInfo], "firstName"))

That retrieves the firstname value into my new custom column. 

Thanks!

Roel

[Dhairya]

Hey @leadmarlin 
From your sample data, it is visible that the source is in JSON data format and you want to extract value corresponding to country, this task can be easily done using power query. Please follow the below steps to achieve it.

Step 1: Open Power Query Editor.
Step 2: Load the JSON File.

Step 3: Convert it to a Table.

Step 4: Pivot the Column.

Step 5: Remove Columns other than Country. 

Now you can load this data into Power BI Desktop by clicking on Close and Apply button on top left corner.
Please refer to the following Advanced Editor Code, you have to just replace my source with yours.

let
Source = Json.Document(File.Contents("D:\sample.json")),
#"Converted to Table" = Record.ToTable(Source),
#"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"Country"})
in
#"Removed Other Columns"

If this helps you then please kudo the post and mark it as accepted as solution so that others can find it quickly while facing the similar issue.