none
how to expand list in columns of table RRS feed

  • Question

  • Hi,

    From a json document, I have a structure like 

    {
    [type = "a", values = {11,12,13}],
    [type = "b", values = {21,22,23}]

    }

    I would like to create a table like

    ---------

    a   |   b

    11 | 21

    12 | 22

    ----------

    I have created a Powerquery like below, but I am stuck here. Please let me know how to create the table. Thanks.

    let  
      Source =  
    {
    [type = "a", values = {11,12}],
    [type = "b", values = {21,22}]
    },
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "values"}, {"Column1.type", "Column1.values"}),
        #"Transposed Table" = Table.Transpose(#"Expanded Column1"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", type any}, {"b", type any}})
    in
        #"Changed Type"

    Monday, February 19, 2018 9:56 PM

Answers

  • As a variant:
    let
        Source = {[type = "a", values = {11,12,13}],[type = "b", values = {21,22,23}]},
        Custom1 = Table.FromRecords(Source),
        Custom2 = #table(Custom1[type],List.Zip(Custom1[values]))
    in
        Custom2


    Maxim Zelensky Excel Inside

    Tuesday, February 20, 2018 7:06 AM

All replies

  • Attempt #1:

    let
        Source = {
    [type = "a", values = {11,12}],
    [type = "b", values = {21,22}],
    [type = "c", values = {31,32}]
    },
        Custom2 = List.Transform(Source, each Record.RemoveFields(Record.RenameFields(_, {"values",Record.Field(_,"type")}), "type")),
        Custom1 = List.Transform(Custom2, Record.FieldValues),
        Custom3 = List.Zip(Custom1),
        Custom4 = Table.FromColumns(Custom3{0}),
        Headers = List.Combine(List.Transform(Custom2, Record.FieldNames)),
        Custom5 = Table.RenameColumns(Custom4, List.Zip({Table.ColumnNames(Custom4), Headers}))
    in
        Custom5

    I'm curious to see the other replies from the community members. I'm probably using a way too complicated approach and there's an easier one that I totally missed

    EDIT: new attempt. I knew that I was overly complicating things

    Attempt #2

    let
        Source = {
    [type = "a", values = {11,12}],
    [type = "b", values = {21,22}],
    [type = "c", values = {31,32}]
    },
        Custom1 = List.Transform(Source, each List.Combine({{[type]}, [values]})),
        Custom2 = Table.FromColumns(Custom1),
        #"Promoted Headers" = Table.PromoteHeaders(Custom2, [PromoteAllScalars=true])
    in
        #"Promoted Headers"

    • Edited by Miguel Escobar Tuesday, February 20, 2018 6:51 AM new attempt
    Tuesday, February 20, 2018 6:40 AM
  • As a variant:
    let
        Source = {[type = "a", values = {11,12,13}],[type = "b", values = {21,22,23}]},
        Custom1 = Table.FromRecords(Source),
        Custom2 = #table(Custom1[type],List.Zip(Custom1[values]))
    in
        Custom2


    Maxim Zelensky Excel Inside

    Tuesday, February 20, 2018 7:06 AM
  • Hi Miguel,

    my take would be this:

    let
        Source = {
    [type = "a", values = {11,12}],
    [type = "b", values = {21,22}],
    [type = "c", values = {31,32}]
    },
        Custom1 = Table.FromRecords(Source),
        Custom2 = #table(Custom1[type],  List.Zip(Custom1[values]))
    in
        Custom2


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, February 20, 2018 7:06 AM
    Moderator
  • lol :D

    Maxim Zelensky Excel Inside

    Tuesday, February 20, 2018 7:08 AM
  • Hi,

    Your answers work well with inline data.  I could not get it to work with data in a json file.  My file sample_data.json is

    {"Source":{"a":[11,12,13],"b":[21,22,23],"c":[31,32,33]}}

    My Powerquery below failed at Custom1.

    let  Data = Json.Document(File.Contents("C:\temp\sample_data.json")),
        Source = Data[Source],
        Custom1 = Table.FromList(Source)
        //Custom2 = #table(Custom1[type],  List.Zip(Custom1[values]))
    
    in
       Custom1

    The error is

    Expression.Error: We cannot convert a value of type Record to type List.
    Details:
        Value=Record
        Type=Type

    How would you get it to work?

    Thanks for your help,

     KT

    Tuesday, February 20, 2018 4:15 PM
  • Well, it not so much different:

    let
        Data = Json.Document(Text.ToBinary("{""Source"":{""a"":[11,12,13],""b"":[21,22,23],""c"":[31,32,33]}}")),
        Source = Data[Source],
        Custom1 = #table(Record.FieldNames(Source), List.Zip(Record.FieldValues(Source)))
    in
        Custom1


    Maxim Zelensky Excel Inside


    Tuesday, February 20, 2018 4:21 PM
  • Maxim,

    This works very well.  

    Thank you very much.

    Kha

    Tuesday, February 20, 2018 6:38 PM
  • Hi,

    Instead of using Powerquery commands to process json documents like your solution, what would be the steps to use point&ciick via Excel GUI to achieve the same result?

    Also MSDN Powerquery reference online documentation seems to be for experience M developer.  Do you know of good onine tutorials? 

    thanks,

    KT

    Wednesday, February 21, 2018 1:04 PM