How to show data properly in Office Excel Using Power Query Editor?

5
votes

I have below JSON output from an API, in Office Excel I am importing data via Web from API.

   [{
        "level": 1,
        "children": [{
            "level": 2,
            "children": [{
                "level": 3,
                "name": "Chandni Chowk",
                "data": ["Data 1", "Data 2"]
            }],
            "name": "Delhi",
            "data": ["Delhi Area"]
        }],
        "name": "Country",
        "data": ["India", "Bangladesh"]
    }]

https://docs.microsoft.com/en-us/powerquery-m/quick-tour-of-the-power-query-m-formula-language

I have above document.

let
    Source = Json.Document(Web.Contents("MY API URL GOES HERE")),
    AsTable = Table.FromRecords(Source)
    ----
    ----
in
    #"Renamed Column2"

In the power query editor I have this for now.

As a out put in Excel file I need like this.

Country       Delhi          Chandni Chowk         
India         Delhi Area     Data 1
Bangladesh                   Data 2

Can I get this data from this JSON or I need to change my JSON output format which matches power query?

1
excelpowerbioffice365powerquerym
I am looking for step by step answer. As I am newbie in Power Query. Thanks – Jackson
Is it always three levels? – Alexis Olson
Yes @AlexisOlson – Jackson

1 Answers

3
votes

Power Query interprets JSON as a hierarchy of records and lists. My goal is to flatten the JSON into a record like this and then convert it into a table:

      Country : {"India", "Bangladesh"}
        Delhi : {"Delhi Area"}
Chandni Chowk : {"Data 1", "Data 2"}

At any particular level, we can pull the name and data value using Record.FromList:

Record.FromList({CurrentLevel[data]}, {CurrentLevel[name]})

For the first level, this is

Record.FromList({{"India","Bangladesh"}}, {"Country"})

which corresponds to the first field in the goal record.

At any level, we can navigate to the next level like this:

NextLevel = CurrentLevel[children]{0}

Using these to building blocks, we can now write a custom function Expand to flatten the record:

1 |    (R as record) as record =>
2 |    let
3 |        ThisLevel = Record.FromList({R[data]}, {R[name]}),
4 |        CombLevel = if Record.HasFields(R, {"children"})
5 |                    then Record.Combine({ThisLevel, @Expand(R[children]{0})})
6 |                    else ThisLevel
7 |    in
8 |        CombLevel

Line 1: The syntax for defining a function. It takes a record R and returns a record after doing some transformations.

Line 3: How to deal with the current level, as mentioned earlier.

Line 4: Check if the record has another level to expand down to.

Line 5: If it does, then Record.Combine the result of the current level with the result of the next level, where the result of the next level is calculated by navigating to the next level and recursively applying the function we're defining. With three levels this looks like:

Record.Combine({Level1, Record.Combine({Level2, Level3})})

Line 6: Recursion stops when there are no more levels to expand. No more combinations, just the last level is returned.

All that's left is to transform it into the shape we want. Here's what my query looks like using the Expand function we just defined:

let
    Source = Json.Document( < JSON Source > ),
    ExpandRecord = Expand(Source{0}),
    ToTable = Table.FromColumns(
                  Record.FieldValues(ExpandRecord),
                  Record.FieldNames(ExpandRecord)
              )
in
    ToTable

This uses Record.FieldValues and Record.FieldName as arguments in Table.FromColumns.

The step after using the Expand custom function looks like this in the query editor if you select the first list cell:

Record

The final result is what you asked for:

Result