Parse issue - DataFromat.Error

[non23]

Hey guys,

Is there a way to get the value for WHATIWANTTOGET which is 12345 using DAX? Tried to parse the column via Power Query but I'm getting "DataFormat.Error" We reached the end of the buffer.

[{"xxx":"UNA","xxx":"21","xxx":"UNA","xxx":"aaa","xxx":"1","xxx":"2","xxx":"12","WHATIWANTTOGET":"12345","XXX":"1234","XXX":"UNA","XXXX":"UNA"}]

Note that the position always changes. Only keyword is WHATIWANTTOGET.

[ValtteriN]

Hi,

I placed the value provided into a column:

Now this measure gets the value:

Get value =

VAR JsonString = MAX('Table (40)'[Column1])

VAR StartPos = SEARCH("WHATIWANTTOGET"":""", JsonString, 1) + LEN("WHATIWANTTOGET"":""")

VAR EndPos = SEARCH("""", JsonString, StartPos)

RETURN MID(JsonString, StartPos, EndPos - StartPos)


End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[non23]

Thanks for this! However, the var JsonString is getting the maximum value for the whole lot. Is there a way to get the one for the row instead? Tried the formula you've shared but it only has one value for all.

[ValtteriN]

Can you elaborate a bit? Are there multiple values? 

[non23]

Yes, there are multiple values. Please see the below samples:

1.[{"ACT":"play","AREA":"UNA","NAME":"UNA","AGE":"UNA","SEX":"UNA","STATUS":"UNA","ORDER":"UNA","WHATIWANT":"35454565","FREQUENT":"UNA","COUNTRY":"UNA","WEATHER":"UNA"}]
2. [{"WHATIWANT":"123456"}]
3. [{"ITEM":"UNA","WHATIWANT":"4565623","COUNTRY":"UNA","ZONE":"UNA"}]

[AnkitKukreja · Accepted solution]

Hi! @non23 

You can use this m-code for your solution and once you get the new column you might have to clean that a bit.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY8xD4JADIX/iulMSLgTBrcGizBwxKMX1DsGdwdXY/zvwqHGDppO7+tL36v3kKX+HgBLDrAJcL2cbwGSCVjCSJzBCAy2JADupO7pIDUju16gzm7JCjLUyM2AZknX+XqaIo+rytLe0Wvz9pedM2yP8gYh1993HyOMiQeVrubXZESm9JTw8ejF0zC1f3rNnQqlfzY4dYZk/PgE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
#"Inserted Text Between Delimiters" = Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([Test], """WHATIWANT"":""", """"), type text)
in
#"Inserted Text Between Delimiters"