topic Re: Parse issue - DataFromat.Error in DAX Commands and Tips

Parse issue - DataFromat.Error

non23 — Fri, 06 Sep 2024 04:40:54 GMT

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.

Re: Parse issue - DataFromat.Error

ValtteriN — Fri, 06 Sep 2024 05:44:01 GMT

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/

Re: Parse issue - DataFromat.Error

non23 — Fri, 06 Sep 2024 05:52:26 GMT

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.

Re: Parse issue - DataFromat.Error

ValtteriN — Fri, 06 Sep 2024 05:59:36 GMT

Can you elaborate a bit? Are there multiple values?

Re: Parse issue - DataFromat.Error

non23 — Fri, 06 Sep 2024 06:13:04 GMT

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"}]

Re: Parse issue - DataFromat.Error

AnkitKukreja — Fri, 06 Sep 2024 12:23:26 GMT

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"