none
[Power Query] List with Record, as Column with Value ? RRS feed

  • Question

  • Hello,

    I am using Power Query for the first time. Until then, I always imported XML files who are easier to deal with.

    But here is a JSON file, and... Power Query is so confusing to me that I can't get what I want, despite googling for two hours.

    I hope you can excuse me if what I am asking is easy for you. I am also sorry that I can't display images directly and only able to give links. Apparently the forum say no until "we are able to verify your account" (?).

    So, I have this kind of JSON data (truncated of course) :

    [   {      "cost_indices":[         {            "activity":"manufacturing",            "cost_index":0.0182         },         {            "activity":"researching_time_efficiency",            "cost_index":0.0225         },         {            "activity":"researching_material_efficiency",            "cost_index":0.0161         },         {            "activity":"copying",            "cost_index":0.013         },         {            "activity":"invention",            "cost_index":0.0598         },         {            "activity":"reaction",            "cost_index":0.001         }      ],      "solar_system_id":30020141   },   {      "cost_indices":[         {            "activity":"manufacturing",            "cost_index":0.005         },         {            "activity":"researching_time_efficiency",            "cost_index":0.001         },         {            "activity":"researching_material_efficiency",            "cost_index":0.001         },         {            "activity":"copying",            "cost_index":0.001         },         {            "activity":"invention",            "cost_index":0.001         },         {            "activity":"reaction",            "cost_index":0.001         }      ],      "solar_system_id":30035042   }]

    I want to get something who will look like that :

    https://i.imgur.com/iQtAzqy.png

    But... The next one is all I managed to achieve. As you can see, there are multiple rows for a same ID. I want to avoid that as I want the "activity" to be a column by itself. But each activity/cost_index are considered as a LIST who each contains a RECORD, so expand said lists will generate a row. I am stuck here :

    https://i.imgur.com/2KUkG2O.png

    I tried to use Table.Pivot() and multiple others things after searching on the web, but I don't manage to get even one modification, to the point that I wonder if the query editor really work. I would have less difficulty to manage raw data using Javascript...

    Obviously, I am bad to this. Can someone tell me how to do this ?

    Cordialy,
    TK



    • Edited by TKACC Friday, July 5, 2019 3:27 PM
    Friday, July 5, 2019 3:27 PM

Answers

  • Hi,

    You may use following approach:

    let
        Source = Json.Document("[  
       {  
          ""cost_indices"":[  
             {  
                ""activity"":""manufacturing"",
                ""cost_index"":0.0182
             },
             {  
                ""activity"":""researching_time_efficiency"",
                ""cost_index"":0.0225
             },
             {  
                ""activity"":""researching_material_efficiency"",
                ""cost_index"":0.0161
             },
             {  
                ""activity"":""copying"",
                ""cost_index"":0.013
             },
             {  
                ""activity"":""invention"",
                ""cost_index"":0.0598
             },
             {  
                ""activity"":""reaction"",
                ""cost_index"":0.001
             }
          ],
          ""solar_system_id"":30020141
       },
       {  
          ""cost_indices"":[  
             {  
                ""activity"":""manufacturing"",
                ""cost_index"":0.005
             },
             {  
                ""activity"":""researching_time_efficiency"",
                ""cost_index"":0.001
             },
             {  
                ""activity"":""researching_material_efficiency"",
                ""cost_index"":0.001
             },
             {  
                ""activity"":""copying"",
                ""cost_index"":0.001
             },
             {  
                ""activity"":""invention"",
                ""cost_index"":0.001
             },
             {  
                ""activity"":""reaction"",
                ""cost_index"":0.001
             }
          ],
          ""solar_system_id"":30035042
       }
    ]"),
        toTable = Table.FromRecords(Source),
        transform = Table.TransformColumns(toTable, {"cost_indices", each Table.PromoteHeaders(Table.Transpose(Table.FromRecords(_)))}),
        reorder = Table.ReorderColumns(transform,{"solar_system_id", "cost_indices"}),
        expand = Table.ExpandTableColumn(reorder, "cost_indices", Table.ColumnNames(reorder[cost_indices]{0}))
    in
        expand

    • Edited by Aleksei Zhigulin Friday, July 5, 2019 4:32 PM
    • Marked as answer by TKACC Saturday, July 6, 2019 3:13 PM
    Friday, July 5, 2019 4:28 PM
  • Or only using the UI

    let
        Source = Json.Document(File.Contents(PathToYourJSONFile)),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cost_indices", "solar_system_id"}, {"cost_indices", "solar_system_id"}),
        #"Expanded {0}1" = Table.ExpandListColumn(#"Expanded {0}", "cost_indices"),
        #"Expanded {0}2" = Table.ExpandRecordColumn(#"Expanded {0}1", "cost_indices", {"activity", "cost_index"}, {"activity", "cost_index"}),
        #"Pivoted Column" = Table.Pivot(#"Expanded {0}2", List.Distinct(#"Expanded {0}2"[activity]), "activity", "cost_index")
    in
        #"Pivoted Column"

    • Marked as answer by TKACC Saturday, July 6, 2019 3:13 PM
    Friday, July 5, 2019 4:41 PM

All replies

  • Hi,

    You may use following approach:

    let
        Source = Json.Document("[  
       {  
          ""cost_indices"":[  
             {  
                ""activity"":""manufacturing"",
                ""cost_index"":0.0182
             },
             {  
                ""activity"":""researching_time_efficiency"",
                ""cost_index"":0.0225
             },
             {  
                ""activity"":""researching_material_efficiency"",
                ""cost_index"":0.0161
             },
             {  
                ""activity"":""copying"",
                ""cost_index"":0.013
             },
             {  
                ""activity"":""invention"",
                ""cost_index"":0.0598
             },
             {  
                ""activity"":""reaction"",
                ""cost_index"":0.001
             }
          ],
          ""solar_system_id"":30020141
       },
       {  
          ""cost_indices"":[  
             {  
                ""activity"":""manufacturing"",
                ""cost_index"":0.005
             },
             {  
                ""activity"":""researching_time_efficiency"",
                ""cost_index"":0.001
             },
             {  
                ""activity"":""researching_material_efficiency"",
                ""cost_index"":0.001
             },
             {  
                ""activity"":""copying"",
                ""cost_index"":0.001
             },
             {  
                ""activity"":""invention"",
                ""cost_index"":0.001
             },
             {  
                ""activity"":""reaction"",
                ""cost_index"":0.001
             }
          ],
          ""solar_system_id"":30035042
       }
    ]"),
        toTable = Table.FromRecords(Source),
        transform = Table.TransformColumns(toTable, {"cost_indices", each Table.PromoteHeaders(Table.Transpose(Table.FromRecords(_)))}),
        reorder = Table.ReorderColumns(transform,{"solar_system_id", "cost_indices"}),
        expand = Table.ExpandTableColumn(reorder, "cost_indices", Table.ColumnNames(reorder[cost_indices]{0}))
    in
        expand

    • Edited by Aleksei Zhigulin Friday, July 5, 2019 4:32 PM
    • Marked as answer by TKACC Saturday, July 6, 2019 3:13 PM
    Friday, July 5, 2019 4:28 PM
  • Or only using the UI

    let
        Source = Json.Document(File.Contents(PathToYourJSONFile)),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"cost_indices", "solar_system_id"}, {"cost_indices", "solar_system_id"}),
        #"Expanded {0}1" = Table.ExpandListColumn(#"Expanded {0}", "cost_indices"),
        #"Expanded {0}2" = Table.ExpandRecordColumn(#"Expanded {0}1", "cost_indices", {"activity", "cost_index"}, {"activity", "cost_index"}),
        #"Pivoted Column" = Table.Pivot(#"Expanded {0}2", List.Distinct(#"Expanded {0}2"[activity]), "activity", "cost_index")
    in
        #"Pivoted Column"

    • Marked as answer by TKACC Saturday, July 6, 2019 3:13 PM
    Friday, July 5, 2019 4:41 PM
  • Thanks you both for your help. BOTH solutions are working fine, I will keep a copy for the future, just in case :)

    @ Bill Szysz > Yes, I realized after reading your answer that I could use Pivot with the GUI. For my defense, I am using the FRENCH version of Excel 2016, so there isn't any "Pivot Column" name in the GUI. It is called "Colonne de tableau croisé dynamique" which didn't make any sense to me yesterday, and it is disabled most of the time so I couldn't test blindly to find out his role. And indeed, it works fine since I find out that it was Pivot Column.

    I don't get the choice of the french name. I mean, we have the verb "pivoter" too, who would be more obvious =) Anyway, it does his job :)

    Saturday, July 6, 2019 3:11 PM