Forum Discussion

smpa01's avatar
smpa01
Community Champion
2 years ago
Solved

Convert text to record

ImkeF AlexisOlson 

 

I am making an API call that returns a json as text like this

 

let
    Source = "{
	""header"": [""[Value]""],
	""rowCount"": 1,
	""data"": [
		[2.0]
	]
}",
    #"Converted to Table" = #table(1, {{Source}})
in
    #"Converted to Table"

 

 I want to retrieve the value of "data" and what is an efficient way of doing it. Is it possible to convert this into a PQ Record with any built-in API so that I can progrmatically access them. It already contains the key value pairs.

 

Thank you in advance.

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    Power Query has built-in JSON parsing.

     

    let
    Source = "{
	""header"": [""[Value]""],
	""rowCount"": 1,
	""data"": [
		[2.0]
	]
}",
    #"Parsed JSON" = Json.Document(Source)
in
    #"Parsed JSON"

     

     

    To grab just 2.0,

    Json.Document(Source)[data]{0}{0}

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    Power Query has built-in JSON parsing.

     

    let
    Source = "{
	""header"": [""[Value]""],
	""rowCount"": 1,
	""data"": [
		[2.0]
	]
}",
    #"Parsed JSON" = Json.Document(Source)
in
    #"Parsed JSON"

     

     

    To grab just 2.0,

    Json.Document(Source)[data]{0}{0}