Hi Forum,
I hope you guys are doing well. I'm struggling with the MS Form Json response and I hope you guys can help me finding a solution.
I created a MS Form that gives the next output in Power Automate: (Response)
{
"responder": "chris@test.com",
"submitDate": "10/31/2023 6:44:11 PM",
"r9922aea683814bd492f0fed90e8f8a3b": "test",
"r74cef0023a0b4f6585b2a7e9b960ab15": "test",
"rceb3def1a94d4d40ac31c3c38ae1cf1d": "test",
"rdc1ebc4aeff14fe6bd6d6e33b5704304": "test",
"r609e044438e64e10be0c003250ad1b43": "",
"re66ff8f03dbc42089e5ef7331c11be15": "",
"redf42a5ba7f041229d8b6f3478d3f85a": "FR",
"re876c4d54fc54f2f89088ca3b65a9265": "Yes",
"r5bbb9eb4a2fe416abb9eb5e7fe590337": "Yes - Use of pro",
"r2b694e30a839485ba912f6be78f4dfbf": "Auth system",
"rf7dc56c06ca94e04aa3028a1d1375cfb": "[{\"name\":\"Background_Chris.png\",\"link\":\"https://my.sharepoint.com/personal/chris/Documents/Apps/Microsoft%20Forms/TestForm/Question%201/Background_Chris%202.png\",\"id\":\"01YJFOYFSOHNN2G6N55BDI3S4EFI6XHFU2\",\"type\":null,\"size\":442712,\"referenceId\":\"01YJYTRYTVUYQ6UPBMVZXZEXQYMKDWJ3K2\",\"driveId\":\"b!tL9edbqlsTEeZe6tq_1zMkK7MNBV2EFFmyWc_vX9ERT4EM4SCw3aSJ8kdIVz72Ce\",\"status\":1,\"uploadSessionUrl\":null}]",
"rf307688714b64d2bb1295475dfb3b7d9": "test",
"rbc3b1b5ed28d4a6ba0e4b3022dbec3fe": "Paper",
"r9b32c1657b214e3aabb064e58fd6acad": "[\"Badges\",\"Opening\",\"Phone\"]"
}
I plan to have more questions and other Forms in the future, so I'm looking for something dynamic to loop through and insert in a PowerBi streaming dataset. The outcome should be an array that I can iterate through. (with or without the responder and submitDate)
[
{
"Question" :"r9922aea683814bd492f0fed90e8f8a3b",
"Respons" :"test"
},
{
"Question" :"r74cef0023a0b4f6585b2a7e9b960ab15",
"Respons" :"test"
},
{
"Question" :"rceb3def1a94d4d40ac31c3c38ae1cf1d",
"Respons" :"test"
},
{
"Question" :"rdc1ebc4aeff14fe6bd6d6e33b5704304",
"Respons" :"test"
},
{
"Question" :"r609e044438e64e10be0c003250ad1b43",
"Respons" :""
},
{
"Question" :"re66ff8f03dbc42089e5ef7331c11be15",
"Respons" :""
},
{
"Question" :"redf42a5ba7f041229d8b6f3478d3f85a",
"Respons" :"FR"
},
{
"Question" :"re876c4d54fc54f2f89088ca3b65a9265",
"Respons" :"Yes"
},
{
"Question" :"r5bbb9eb4a2fe416abb9eb5e7fe590337",
"Respons" :"Yes - Use of pro"
},
{
"Question" :"r2b694e30a839485ba912f6be78f4dfbf",
"Respons" :"Auth system"
},
{
"Question" :"rf7dc56c06ca94e04aa3028a1d1375cfb",
"Respons" :"[{\"name\":\"Background_Chris.png\",\"link\":\"https://my.sharepoint.com/personal/chris/Documents/Apps/Microsoft%20Forms/TestForm/Question%201/Background_Chris%202.png\",\"id\":\"01YJFOYFSOHNN2G6N55BDI3S4EFI6XHFU2\",\"type\":null,\"size\":442712,\"referenceId\":\"01YJYTRYTVUYQ6UPBMVZXZEXQYMKDWJ3K2\",\"driveId\":\"b!tL9edbqlsTEeZe6tq_1zMkK7MNBV2EFFmyWc_vX9ERT4EM4SCw3aSJ8kdIVz72Ce\",\"status\":1,\"uploadSessionUrl\":null}]"
},
{
"Question" :"rf307688714b64d2bb1295475dfb3b7d9",
"Respons" :"test"
},
{
"Question" :"rbc3b1b5ed28d4a6ba0e4b3022dbec3fe",
"Respons" :"Paper"
},
{
"Question" :"r9b32c1657b214e3aabb064e58fd6acad",
"Respons" :"[\"Badges\",\"Opening\",\"Phone\"]"
}
]
I have tried playing with xpath, without results, extracting the value works but not the node name. The closest I have been is following the next tutorial https://www.youtube.com/watch?v=SF7x2MRCRsQ that explains how to convert to a CSV table first and then back to JSON, but because the values contain comma's it's not giving the right result. I tried replacing them but I can't distinguish the JSON and values commas.
I really hope you guys can point me in the right direction.
Thank you in advance and looking forward.
Chris
