Question 397110 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Extract JSON Array to same Row using Excel Power Query

I have the following JSON

{
    "Data": {
        "totalExecutionTime": "00:00:00.5544916",
        "totalCount": "33414",
        "Items": [
            {
                "featureId": 100,
                "featureName": "Product",
                "id": 5903,
                "parentId": 0,
                "brand": "Normal",
                "order": 0,
                "createdDate": "2022-08-18T19:00:57.44",
                "modifiedDate": "2024-07-10T13:21:29.123",
                "Values": [
                    {
                        "featureId": 100,
                        "featureName": "Value",
                        "seq": 0,
                        "modifiedDate": "2022-08-18T19:03:05.84",
                        "value": "AMA83257"
                    },
                    {
                        "featureId": 281,
                        "featureName": "RetailPrice",
                        "seq": 0,
                        "modifiedDate": "2022-08-18T20:30:37.503",
                        "value": "155"
                    }
                ]
            },
            {
                "featureId": 100,
                "featureName": "Product",
                "id": 5905,
                "parentId": 0,
                "brand": "Normal",
                "order": 0,
                "createdDate": "2022-08-18T19:00:57.44",
                "modifiedDate": "2024-07-09T07:01:15.36",
                "Values": [
                    {
                        "featureId": 100,
                        "featureName": "Value",
                        "seq": 0,
                        "modifiedDate": "2022-08-18T19:03:05.84",
                        "value": "AMAXE46071"
                    },
                    {
                        "featureId": 281,
                        "featureName": "RetailPrice",
                        "seq": 0,
                        "modifiedDate": "2023-06-05T14:45:54.257",
                        "value": "245.2"
                    }
                ]
            }
        ]
    }
}

I need to get the data to excel in this format

Item Price
AMA83257 155
AMAXE46071 245.2
Tags:

2

Answers


  1. 0

    enter image description here

    let
    Source = Json.Document(File.Contents("C:UsersDavOneDriveDesktoptest.json")),
    Data = Source[Data],
    Items = Data[Items],
    #"Converted to Table" = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1][Values]{0}[value]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Column1][Values]{1}[value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Item"}, {"Custom.1", "Price"}})
in
    #"Renamed Columns"
    Login or Signup to reply.
  2. 0

    Try this in powerquery

    let Source = Json.Document(File.Contents("C:tempa.json")),
Items = Source[Data][Items],
x = Table.Combine(List.Transform(Items, each #table({"Item", "Price"}, {{_[Values]{0}[value],_[Values]{1}[value]}})))
in  x

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search