Forum Discussion
Creating new columns from a column with keys and a column with values
Hi,
I am starting to use powerBI Desktop and I would like to import my data from a json (generated by a graphQL API).
I am now able to authenticate to my API and return my graphQL JSON but the format is not usable as it is, I need to modify some columns.
I have rows containing data. For each data I have columns like id, title, ... and a "fields" column.
In this "fields" column I have a list of record.
Each record contains a "key" attribute and a "value" attribute (the value can be a string or a list of ids if it must be linked to other tables).
My goal is to convert all these fields into columns named as the "key" and with a cell filled with "value".
This way, I can have a single row for each data with a column for each field.
Here are some screenshots to explain :
1) 1 row with a data, its title, fields, ... (we can have more than 1 row)
2) When we expand the fields of the data
3) When we extract the fields of the data
Could you help me to produce a code able to produce the result shown in the last picture?
Thank you!
*******
A json example with ony 1 data:
{
"data": {
"all": {
"data": [
{
"id": 56,
"title": "NEXESS",
"fields": [
{
"key": "automation",
"value": null
},
{
"key": "startup-ataglance",
"value": "Contactless and robust chips to track tools and equipment in harsh industrial environment "
},
{
"key": "startup-automation",
"value": null
},
{
"key": "startup-avis",
"value": null
},
{
"key": "startup-avis2",
"value": "The chips are very expensive compared to basic RFID chips. Therefore, they ..."
},
{
"key": "startup-businessmodel",
"value": "BM: one shot purchase.\r\n"
},
{
"key": "startup-ca",
"value": "2 ME (2013)"
},
{
"key": "startup-city",
"value": "Paris "
},
{
"key": "startup-competitor",
"value": null
},
{
"key": "startup-costs",
"value": "Costs: A smart RFID cabinet costs 15,000 euros. A PDA costs 10,000 euros. The price of a RFID tag is between "
},
{
"key": "startup-country",
"value": "France"
},
{
"key": "startup-creationdate",
"value": "2008-01-01"
},
{
"key": "startup-description",
"value": "NEXESS uses the RFID technology : RFID is a …"
},
{
"key": "startup-differenciation",
"value": "More robust chips than other RFID chips. Specially designed for harsh environenment such as a nuclear power plant."
},
{
"key": "startup-employees",
"value": "25 (2013)"
},
{
"key": "startup-expertise",
"value": null
},
{
"key": "startup-files",
"value": null
},
{
"key": "startup-forum",
"value": null
},
{
"key": "startup-forum-prive",
"value": null
},
{
"key": "startup-funding",
"value": "50% EDF (spin-off of DPN - Nuclear Generation Division)"
},
{
"key": "startup-fundraising",
"value": null
},
{
"key": "startup-howtopublish",
"value": null
},
{
"key": "startup-interest",
"value": "The chips are very expensive compared to basic RFID chips. Therefore, they ..."
},
{
"key": "startup-interest2",
"value": "-"
},
{
"key": "startup-keywordnew",
"value": "{\"idsToRemove\":[],\"idsToAdd\":[],\"idsPresent\":[\"7734\",\"332\",\"325\",\"331\",\"65\",\"5036\"]}"
},
{
"key": "startup-label",
"value": null
},
{
"key": "startup-log",
"value": null
},
{
"key": "startup-marketassesment",
"value": "The chips are very expensive compared to basic RFID chips. Therefore, they ..."
},
{
"key": "startup-marketsegment",
"value": "Industrial plants\r\nConstruction sector"
},
{
"key": "startup-name",
"value": "NEXESS"
},
{
"key": "startup-noteoi",
"value": null
},
{
"key": "startup-oitake",
"value": "Market : The chips are very expensive compared to ..."
},
{
"key": "startup-origine",
"value": "VC - Others"
},
{
"key": "startup-references",
"value": "Almost all EDF nuclear power plants in France already use Nexess’ solutions."
},
{
"key": "startup-region",
"value": "Ile-de-France"
},
{
"key": "startup-revenues",
"value": null
},
{
"key": "startup-statushistoric",
"value": null
},
{
"key": "startup-subterritory",
"value": "Paris (75)"
},
{
"key": "startup-teamassessment",
"value": "The CTO seems to have quite an extensive knowledge of the technology. However, was not able to demonstrate clearly the added value for the client."
},
{
"key": "startup-technicalassessment",
"value": "Strong R&D effort to develop robust chips. Pilots with EDF nuclear power plants have validated the robustness of the chips."
},
{
"key": "startup-todo",
"value": null
},
{
"key": "startup-trl",
"value": "Commercial Solution (TRL 9)"
},
{
"key": "startup-valueproposition",
"value": "Founded in 2008, Nexess offers a wide range of ..."
},
{
"key": "startup-visuel",
"value": null
},
{
"key": "startup-visuelsolution",
"value": null
},
{
"key": "startup-website",
"value": "[{\"link\":\"http:\\\/\\\/www.nexess-solutions.com\\\/fr\\\/\",\"title\":\"Site Internet\"},{\"link\":\"https:\\\/\\\/www.linkedin.com\\\/company\\\/475163?trk=tyah\",\"title\":\"LinkedIn\"}]"
},
{
"key": "startup-workforce",
"value": "25 (2013)"
},
{
"key": "startup-zone",
"value": "France"
},
{
"key": "statut",
"value": "{\"id\":195,\"uik\":\"status3\",\"color\":\"rgb(145, 228, 255)\",\"title\":\"3 - POC\"}"
},
{
"key": "startup-referentoi",
"value": [
126
]
},
{
"key": "startup-theme",
"value": [
122913,
122933,
122943,
122953,
122973,
122983,
122993,
123043,
123053
]
},
{
"key": "startup-opportunities",
"value": [
67,
187,
296,
297,
298,
2869,
2911,
3027,
3704,
3769,
3885,
4129,
9746,
186
]
},
{
"key": "startup-contact",
"value": [
335,
2216,
2409
]
},
{
"key": "startup-ecosystemlinks",
"value": [
9595
]
}
],
"status": {
"id": 195,
"uik": null,
"title": null
},
"datatype": {
"uik": null
},
"creatorId": 96,
"user": null
}
]
}
}
}
11 Replies
Please provide sanitized sample JSON data that fully covers your issue. Paste the data into your post or use one of the file services. Please show the expected outcome.
Hi Anonymous
You can use the Pivot Column feature. Select data.all.data.fields.key column and click on Pivot Column. Use data.all.data.fields.value column as Values column and expand Advanced options to select Don't Aggregate.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Hi @zephyx
You can use the Pivot Column feature. Select data.all.data.fields.key column and click on Pivot Column. Use data.all.data.fields.value column as Values column and expand Advanced options to select Don't Aggregate.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Yes, thank you for your help!
Just before I saw your post, I watched this video that taught me how to do it (on top of managing multiple data) 🙂https://www.youtube.com/watch?v=pj9xbe1Sp3A
Now before applying this, I have to find how with only one query, I can create multiple tables based on the value of a specific column. 🙂Anonymous Can you give an example? What should the multiple tables be like?
Hi v-jingzhang
Sorry for the late reply, I had to move to another task earlier this week.
So, my goal is to create multiple tables with one source/query.
For example, from this json:{
"data": {
"all": {
"data": [
{
"id": 56,
"title": "NEXESS",
"fields": [
{
"key": "automation",
"value": null
},
{
"key": "startup-ataglance",
"value": "Contactless and robust chips to track tools and equipment in harsh industrial environment "
},
{
"key": "startup-city",
"value": "Paris "
},
{
"key": "statut",
"value": "{\"id\":195,\"uik\":\"status3\",\"color\":\"rgb(145, 228, 255)\",\"title\":\"3 - POC\"}"
},
{
"key": "startup-theme",
"value": [
122913,
122933,
122943,
122953,
122973,
122983,
122993,
123043,
123053
]
},
{
"key": "startup-contact",
"value": [
335,
2216,
2409
]
}
],
"status": "online",
"datatype": "startup",
"creatorId": 96
},
{
"id": 56,
"title": "YOOMAP",
"fields": [
{
"key": "automation",
"value": null
},
{
"key": "startup-ataglance",
"value": "Blabla "
},
{
"key": "startup-city",
"value": "Paris "
},
{
"key": "statut",
"value": "{\"id\":195,\"uik\":\"status3\",\"color\":\"rgb(145, 228, 255)\",\"title\":\"3 - POC\"}"
},
{
"key": "startup-theme",
"value": [
123043,
123053,
123063
]
},
{
"key": "startup-contact",
"value": [
2411
]
}
],
"status": "offline",
"datatype": "startup",
"creatorId": 96
},
{
"id": 56,
"title": "John Doe",
"fields": [
{
"key": "contact-firstname",
"value": "John"
},
{
"key": "contact-lastname",
"value": "Doe"
},
{
"key": "contact-phone",
"value": "0000000000"
}
],
"status": "online",
"datatype": "contact",
"creatorId": 96
}
]
}
}
}I want to generate a table with the first 2 data (they have the same "datatype" property) and another table with the last data that have a different "datatype".
This is a basic example, but I can have more than 2 different datatypes in this json.
And once I split those data into multiple tables, I will apply what we saw previously (pivot on columns fields.key, fields.value). I don't want to apply it before the split because the fields key can be different between datatypes.
The goal here is to minimize the number of operations to be performed by the final user (using power bi), so that he just has to create a query with the code we provide him to retrieve all his schema and tables ready to import.
