Creating new columns from a column with keys and a column with values

[Anonymous]

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

4) What I want to achieve

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
        }
      ]
    }
  }
}

[Anonymous]

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

[v-jingzhang]

@Anonymous Can you give an example? What should the multiple tables be like?

[Anonymous]

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.

[v-jingzhang]

Hi @Anonymous Can you edit or repost the message? Not sure why but I cannot see anything only blank...

[Anonymous]

I have cleaned up the generated HTML.

Is it better?

[lbendlin]

Agree with @v-jingzhang - you cannot at current create new data sources dynamically.  You can however prepare all possible queries (datatype based) and accept that some of them would come back empty.

Here's some boiler plate code.

let
    Source = Json.Document(File.Contents("C:\Users\xxx\Downloads\test.json")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded data" = Table.ExpandRecordColumn(#"Converted to Table", "data", {"all"}, {"data.all"}),
    #"Expanded data.all" = Table.ExpandRecordColumn(#"Expanded data", "data.all", {"data"}, {"data.all.data"}),
    #"Expanded data.all.data" = Table.ExpandListColumn(#"Expanded data.all", "data.all.data"),
    #"Expanded data.all.data1" = Table.ExpandRecordColumn(#"Expanded data.all.data", "data.all.data", {"id", "title", "fields", "status", "datatype", "creatorId"}, {"data.all.data.id", "data.all.data.title", "data.all.data.fields", "data.all.data.status", "data.all.data.datatype", "data.all.data.creatorId"}),
    #"Expanded data.all.data.fields" = Table.ExpandListColumn(#"Expanded data.all.data1", "data.all.data.fields"),
    #"Expanded data.all.data.fields1" = Table.ExpandRecordColumn(#"Expanded data.all.data.fields", "data.all.data.fields", {"key", "value"}, {"data.all.data.fields.key", "data.all.data.fields.value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded data.all.data.fields1", each ([data.all.data.datatype] = "startup")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"data.all.data.title", "data.all.data.fields.key", "data.all.data.fields.value", "data.all.data.status"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"data.all.data.title", "data.all.data.status", "data.all.data.fields.key", "data.all.data.fields.value"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[data.all.data.fields.key]), "data.all.data.fields.key", "data.all.data.fields.value")
in
    #"Pivoted Column"

[v-jingzhang]

Hi @Anonymous 

Yes, it's better now. So your purpose is to split the table into several tables so data sharing the same "datatype" property are grouped into the same new table, right? Is the number of all datatypes fixed or dynamic in every json file? Do you know all possible values of "datatype" property in advance? Will there be new "datatype" values in the future?

At present it looks like we need to create dynamic number of new queries based on the number of "datatype" values. I can only think of how to create new queries manually based on known "datatypes" in original table. If it is dynamic, it is much more difficult. 

Best Regards,
Jing

[Anonymous]

Hi @v-jingzhang, Hi @lbendlin 

Thank you for your replies.

Ideally it would have been dynamic, but, after talking to some people, we should be able to handle this manually if it's easier.

So at this point I'm just missing one last thing, I haven't figured out how to do it easily.
The new goal now is to give a list of those datatypes to the power bi user.

I currently have this code in my power query : 

let
Source = (username as text, password as text) =>
let
   ... (call my graphql query and everything else using the username and password parameters)

Is there a simple way to include a list parameter in the same code (without creating a parameter outside this query) like this ?

let
Source = (username as text, password as text, datatype as list({"startup", "project", ...})) =>
let
   ...

I would like to be able to provide the power bi user with the complete list available with a single query (like this, he just has to duplicate it as many times as he wants and select the right datatype to import each time).

Best Regards.

[Syndicate_Admin]

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.

[v-jingzhang]

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.

[lbendlin]

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.