Forum Discussion
Parse issue - DataFromat.Error
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.
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"
5 Replies
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/
