Contenu Json à exploiter

Accueil – Le CFO masqué Forums Power Query 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 4 mois.
9 sujets de 1 à 9 (sur un total de 9)
  • Auteur
    Articles
  • #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.

    #136732
    Xavier Allart
    Participant

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

    #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

    #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.
    #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.
    #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.
    #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

    #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”

    #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.
9 sujets de 1 à 9 (sur un total de 9)
  • Vous devez être connecté pour répondre à ce sujet.