Contenu Json à exploiter

Ce sujet contient 8 réponses, 2 participants et a été mis à jour pour la dernière fois par beletot, le il y a 8 mois et 2 semaines.

9 sujets de 1 à 9 (sur un total de 9)

Auteur

Articles
23 janvier 2024 à 9 h 27 min #136728

beletot
Participant

Bonjour,
je débute power query.
J’ai un contenu json venant d’une api du site web jotform.
Jotform pour résumer te permet de créer des formulaires en ligne et l’api me permet de récupérer les soumissions.
Voici le json en question, je l’ai mis en pièce jointe
{
“responseCode”: 200,
“message”: “success”,
“content”: [
{
“id”: “5814824366112297208”,
“form_id”: “203372346517353”,
“ip”: “109.136.206.116”,
“created_at”: “2024-01-19 09:07:17”,
“status”: “ACTIVE”,
“new”: “1”,
“flag”: “0”,
“notes”: “”,
“updated_at”: null,
“answers”: {
“1”: {
“name”: “testDe”,
“order”: “1”,
“text”: “Test de n\u00e9erlandais – DutchTest – Positionnement “,
“type”: “control_head”
},
“2”: {
“name”: “informationsPersonnelles”,
“order”: “6”,
“text”: “Informations personnelles”,
“type”: “control_head”
},
“3”: {
“name”: “prenom”,
“order”: “7”,
“text”: “Pr\u00e9nom”,
“type”: “control_textbox”,
“answer”: “Nicolas”
},
“4”: {
“name”: “nom”,
“order”: “8”,
“text”: “NOM”,
“type”: “control_textbox”,
“answer”: “Genevois”
}
}
},
{
“id”: “5814809089491650348”,
“form_id”: “203372346517353”,
“ip”: “109.139.4.9”,
“created_at”: “2024-01-19 08:41:49”,
“status”: “ACTIVE”,
“new”: “1”,
“flag”: “0”,
“notes”: “”,
“updated_at”: null,
“answers”: {
“1”: {
“name”: “testDe”,
“order”: “1”,
“text”: “Test de n\u00e9erlandais – DutchTest – Positionnement “,
“type”: “control_head”
},
“2”: {
“name”: “informationsPersonnelles”,
“order”: “6”,
“text”: “Informations personnelles”,
“type”: “control_head”
},
“3”: {
“name”: “prenom”,
“order”: “7”,
“text”: “Pr\u00e9nom”,
“type”: “control_textbox”,
“answer”: “Noah”
},
“4”: {
“name”: “nom”,
“order”: “8”,
“text”: “NOM”,
“type”: “control_textbox”,
“answer”: “Osoko”
}
}
}
],
“duration”: “59.28ms”,
“resultSet”: {
“offset”: 0,
“limit”: 2,
“orderby”: “created_at”,
“count”: 2
}
}

Content me liste mes réponses et answers me donne les champs utilisé dans le formulaire.
Par exemple
“answers”: {
“1”: {
“name”: “testDe”,
“order”: “1”,
“text”: “Test de n\u00e9erlandais – DutchTest – Positionnement “,
“type”: “control_head”
},
Est une étiquète dans le formulaire, sans grand intérêt pour moi

Par contre
“3”: {
“name”: “prenom”,
“order”: “7”,
“text”: “Pr\u00e9nom”,
“type”: “control_textbox”,
“answer”: “Noah”
},
Est une valeur qui m’intéresse. : c’est un champ de type control_textbox qui demande le prénom de l’utilisateur qui est Noah dans ce cas.

Donc au final via power query, je voudrais récupérer
id | form_id | prenom | nom
5814824366112297208 | 203372346517353 | Nicolas | Genevois
5814809089491650348 | 203372346517353 | Noah | Osoko
J’arrive à avoir des données mais c’est atrocement statique.
Le retour de l’api gardera le même schéma mais le type de champs et le nombre de ligne peut fortement changer.

23 janvier 2024 à 10 h 00 min #136732

Xavier Allart
Participant

Bonjour
Je ne trouve pas le fichier json en piece jointe.
Amicalement

24 janvier 2024 à 1 h 42 min #136782

beletot
Participant

Bonjour Xavier,

J’ai rajouté le fichier json en pièce jointe, à voir si le site web va l’accepter.
Et là c’est le code que j’ai réalisé avec l’aide de google bard et co.
let
//working
// Load the JSON data from the file
data = Json.Document(File.Contents(“C:\wamp64\www\webPro – Jotform\_dev\json\2submissions\submissions.json”)),

// Get the content array
content = data[content],

// Convert the content array to a table
//submissions = Table.FromList(content, Splitter.SplitByNothing()),
submissions = Table.FromList(content, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Create a new table with the desired columns
cleanedData = Table.ExpandRecordColumn(submissions, “Column1”, {“id”, “form_id”, “answers”}, {“Column1.id”, “Column1.form_id”, “Column1.answers”}),
#”Column1.answers développé” = Table.ExpandRecordColumn(cleanedData, “Column1.answers”, {“3”, “4”}, {“Column1.answers.3”, “Column1.answers.4″}),
#”Column1.answers.3 développé” = Table.ExpandRecordColumn(#”Column1.answers développé”, “Column1.answers.3”, {“answer”}, {“Column1.answers.3.answer”}),
#”Column1.answers.4 développé” = Table.ExpandRecordColumn(#”Column1.answers.3 développé”, “Column1.answers.4”, {“answer”}, {“Column1.answers.4.answer”}),
#”Colonnes renommées” = Table.RenameColumns(#”Column1.answers.4 développé”,{{“Column1.answers.3.answer”, “Prenom”}, {“Column1.answers.4.answer”, “Nom”}})
in
#”Colonnes renommées”

C’est une version complètement statique mais je voudrais un code plus dynamique.
Les colonnes id | form_id seront toujours présente mais pas les autres tel que prénom et nom, elles peuvent varier.
Pour le moment je n’arrive pas à utiliser un code avec un each.

Bonne journée

Bertrand

24 janvier 2024 à 1 h 43 min #136783

beletot
Participant

Bon l’extension json ne passe pas.
je vais essayer avec .txt et un zip.

Attachments:
You must be logged in to view attached files.

24 janvier 2024 à 3 h 03 min #136788

Xavier Allart
Participant

Bonjour

Voici je que j’ai compris, l’idée est d’avoir un code permettant de récupérer les réponses de questionnaires ne connaissancant pas à l’avance le nombre de questions et le nom de celles-ci

Voici une solution pour répondre au probleme
Pour le nombre de questions, il faut definir une limite (nombre maxi) j’ai choisi 7 dans mon exemple

let
//working
// Load the JSON data from the file
data = Json.Document(File.Contents(“D:\Telechargement\submissions.json”)),

// Get the content array
content = data[content],

// Convert the content array to a table
//submissions = Table.FromList(content, Splitter.SplitByNothing()),
submissions = Table.FromList(content, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
//Developper que les colonnes “id”, “answers”
#”Column1 développé” = Table.ExpandRecordColumn(submissions, “Column1”, {“id”, “answers”}, {“id”, “answers”}),
// Developper “answers” pour un max de 7 questions. si besoin completer les 2 listes
#”answers développé” = Table.ExpandRecordColumn(#”Column1 développé”, “answers”, {“1”, “2”, “3”, “4”, “5”, “6”, “7”}, {“1”, “2”, “3”, “4”, “5”, “6”, “7”}),
// Pivoter les colonnes “answers” en ligne
#”Supprimer le tableau croisé dynamique des autres colonnes” = Table.UnpivotOtherColumns(#”answers développé”, {“id”}, “Attribut”, “Valeur”),
// Developper les attributs “answer”, “name” de la colonne “Valeur”
#”Valeur développé” = Table.ExpandRecordColumn(#”Supprimer le tableau croisé dynamique des autres colonnes”, “Valeur”, {“answer”, “name”}, {“answer”, “name”}),
//Supprimer la colonne “Attribut”
#”Colonnes supprimées” = Table.RemoveColumns(#”Valeur développé”,{“Attribut”}),
//Pivoter en colonne “name” avec comme colonne de valeur “answer”, et en option avancé : non agreger
#”Colonne dynamique” = Table.Pivot(#”Colonnes supprimées”, List.Distinct(#”Colonnes supprimées”[name]), “name”, “answer”)
in
#”Colonne dynamique”

Attachments:
You must be logged in to view attached files.

24 janvier 2024 à 4 h 56 min #136795

beletot
Participant

Super ton code 🙂
J’ai pris le temps de le comprendre, je l’ai adapté.
Je rajoute la colonne content->form_id
et j’effectue un filtre sur content->answers[]type = control_textbox

J’ai bien compris ta ligne : = Table.ExpandRecordColumn(#”Column1 développé”, “answers”, {“1”, “2”, “3”, “4”, “5”, “6”, “7”}, {“1”, “2”, “3”, “4”, “5”, “6”, “7”})
Par contre mon fichier json complet, content->answers[] comporte 240 lignes.
je sais c’est énorme, en sachant que ce nombre est variable.
Tu dis qu’il faut définir une valeur max (7 dans ton exemple) il n’y a pas moyen de créer les colonnes de façon dynamique ?
A titre d’exemple, je joins le fichier json complet.
Un grand merci pour ton aide, je n’aurais pas trouvé la logique moi-même.

Attachments:
You must be logged in to view attached files.

24 janvier 2024 à 5 h 31 min #136798

Xavier Allart
Participant

bonjour

Une solution simpliste avec Excel pour 250 réponses, pas d’idée comment faire en power query directement :

{“1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″,”10″,”11″,”12″,”13″,”14″,”15″,”16″,”17″,”18″,”19″,”20″,”21″,”22″,”23″,”24″,”25″,”26″,”27″,”28″,”29″,”30″,”31″,”32″,”33″,”34″,”35″,”36″,”37″,”38″,”39″,”40″,”41″,”42″,”43″,”44″,”45″,”46″,”47″,”48″,”49″,”50″,”51″,”52″,”53″,”54″,”55″,”56″,”57″,”58″,”59″,”60″,”61″,”62″,”63″,”64″,”65″,”66″,”67″,”68″,”69″,”70″,”71″,”72″,”73″,”74″,”75″,”76″,”77″,”78″,”79″,”80″,”81″,”82″,”83″,”84″,”85″,”86″,”87″,”88″,”89″,”90″,”91″,”92″,”93″,”94″,”95″,”96″,”97″,”98″,”99″,”100″,”101″,”102″,”103″,”104″,”105″,”106″,”107″,”108″,”109″,”110″,”111″,”112″,”113″,”114″,”115″,”116″,”117″,”118″,”119″,”120″,”121″,”122″,”123″,”124″,”125″,”126″,”127″,”128″,”129″,”130″,”131″,”132″,”133″,”134″,”135″,”136″,”137″,”138″,”139″,”140″,”141″,”142″,”143″,”144″,”145″,”146″,”147″,”148″,”149″,”150″,”151″,”152″,”153″,”154″,”155″,”156″,”157″,”158″,”159″,”160″,”161″,”162″,”163″,”164″,”165″,”166″,”167″,”168″,”169″,”170″,”171″,”172″,”173″,”174″,”175″,”176″,”177″,”178″,”179″,”180″,”181″,”182″,”183″,”184″,”185″,”186″,”187″,”188″,”189″,”190″,”191″,”192″,”193″,”194″,”195″,”196″,”197″,”198″,”199″,”200″,”201″,”202″,”203″,”204″,”205″,”206″,”207″,”208″,”209″,”210″,”211″,”212″,”213″,”214″,”215″,”216″,”217″,”218″,”219″,”220″,”221″,”222″,”223″,”224″,”225″,”226″,”227″,”228″,”229″,”230″,”231″,”232″,”233″,”234″,”235″,”236″,”237″,”238″,”239″,”240″,”241″,”242″,”243″,”244″,”245″,”246″,”247″,”248″,”249″,”250”}, {“1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″,”10″,”11″,”12″,”13″,”14″,”15″,”16″,”17″,”18″,”19″,”20″,”21″,”22″,”23″,”24″,”25″,”26″,”27″,”28″,”29″,”30″,”31″,”32″,”33″,”34″,”35″,”36″,”37″,”38″,”39″,”40″,”41″,”42″,”43″,”44″,”45″,”46″,”47″,”48″,”49″,”50″,”51″,”52″,”53″,”54″,”55″,”56″,”57″,”58″,”59″,”60″,”61″,”62″,”63″,”64″,”65″,”66″,”67″,”68″,”69″,”70″,”71″,”72″,”73″,”74″,”75″,”76″,”77″,”78″,”79″,”80″,”81″,”82″,”83″,”84″,”85″,”86″,”87″,”88″,”89″,”90″,”91″,”92″,”93″,”94″,”95″,”96″,”97″,”98″,”99″,”100″,”101″,”102″,”103″,”104″,”105″,”106″,”107″,”108″,”109″,”110″,”111″,”112″,”113″,”114″,”115″,”116″,”117″,”118″,”119″,”120″,”121″,”122″,”123″,”124″,”125″,”126″,”127″,”128″,”129″,”130″,”131″,”132″,”133″,”134″,”135″,”136″,”137″,”138″,”139″,”140″,”141″,”142″,”143″,”144″,”145″,”146″,”147″,”148″,”149″,”150″,”151″,”152″,”153″,”154″,”155″,”156″,”157″,”158″,”159″,”160″,”161″,”162″,”163″,”164″,”165″,”166″,”167″,”168″,”169″,”170″,”171″,”172″,”173″,”174″,”175″,”176″,”177″,”178″,”179″,”180″,”181″,”182″,”183″,”184″,”185″,”186″,”187″,”188″,”189″,”190″,”191″,”192″,”193″,”194″,”195″,”196″,”197″,”198″,”199″,”200″,”201″,”202″,”203″,”204″,”205″,”206″,”207″,”208″,”209″,”210″,”211″,”212″,”213″,”214″,”215″,”216″,”217″,”218″,”219″,”220″,”221″,”222″,”223″,”224″,”225″,”226″,”227″,”228″,”229″,”230″,”231″,”232″,”233″,”234″,”235″,”236″,”237″,”238″,”239″,”240″,”241″,”242″,”243″,”244″,”245″,”246″,”247″,”248″,”249″,”250”})

A1:A250 liste des nombres de 1 à 250
D3 =JOINDRE.TEXTE(“,”;1;CAR(34)&A1:A250&CAR(34))
D5 =”{“&D3&”}, {“&D3&”})”

Copier D5 dans le code power query

Amicalement

24 janvier 2024 à 6 h 05 min #136799

Xavier Allart
Participant

Voici une autre proposition plus propre en power query, trouvée ici :
https://exceed.hr/blog/dynamically-expand-table-or-record-columns-in-power-query/

Remplacer la ligne par
= Table.ExpandRecordColumn(#”Column1 développé”, “answers”, Record.FieldNames(#”Column1 développé”{0}[answers]), Record.FieldNames(#”Column1 développé”{0}[answers]))

Code complete
let
//working
// Load the JSON data from the file
data = Json.Document(File.Contents(“D:\Telechargement\submissions.json”)),

// Get the content array
content = data[content],

// Convert the content array to a table
//submissions = Table.FromList(content, Splitter.SplitByNothing()),
submissions = Table.FromList(content, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
//Developper que les colonnes “id”, “answers”

#”Column1 développé” = Table.ExpandRecordColumn(submissions, “Column1”, {“id”, “answers”}, {“id”, “answers”}),
// Developper “answers” nombre de colonnes automatique
#”answers développé 1″ = Table.ExpandRecordColumn(#”Column1 développé”, “answers”, Record.FieldNames(#”Column1 développé”{0}[answers]), Record.FieldNames(#”Column1 développé”{0}[answers])),
// Pivoter les colonnes “answers” en ligne
#”Supprimer le tableau croisé dynamique des autres colonnes” = Table.UnpivotOtherColumns(#”answers développé 1″, {“id”}, “Attribut”, “Valeur”),
// Developper les attributs “answer”, “name” de la colonne “Valeur”
#”Valeur développé” = Table.ExpandRecordColumn(#”Supprimer le tableau croisé dynamique des autres colonnes”, “Valeur”, {“answer”, “name”}, {“answer”, “name”}),
//Supprimer la colonne “Attribut”
#”Colonnes supprimées” = Table.RemoveColumns(#”Valeur développé”,{“Attribut”}),
//Pivoter en colonne “name” avec comme colonne de valeur “answer”, et en option avancé : non agreger
#”Colonne dynamique” = Table.Pivot(#”Colonnes supprimées”, List.Distinct(#”Colonnes supprimées”[name]), “name”, “answer”)
in
#”Colonne dynamique”

24 janvier 2024 à 8 h 27 min #136804

beletot
Participant

Ah oui c’est nickel la capacité d’accéder à la valeur de Record.FieldNames(#”Column1 développé”{0}[answers])
Franchement c’est super sympa de passé du temps sur ma question.Tu me fais gagner beaucoup de temps.

J’ai découvert power query cette semaine, je commence à piger la logique derrière.
Prochaine étape : gérer les autres types de champs. (cfr autresType.png)
Le type control radio est simple pour le type control_checkbox je suppose que le plus simple serait de concaténer answer 0 avec 1 ?

Bertrand

Attachments:
You must be logged in to view attached files.
Auteur

Articles

9 sujets de 1 à 9 (sur un total de 9)

Vous devez être connecté pour répondre à ce sujet.

Contenu Json à exploiter

Attachments:

Attachments:

Attachments:

Attachments:

Ceux et celles qui ont fourni le plus de réponses sur ce forum

Avant de poser une question assurez-vous que :

Votre besoin dépasse les limites de ce forum?

Abonnez-vous à nos communications!