Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Excel power query - Tranform JSON data correctly

I have the issue that I do not know how to transform the data correctly to get it in a table. The data is coming from an API in JSON. I can transform the data into a table, but I get for each name a ...
  • Anonymous's avatar
    Anonymous
    6 years ago

    in this way (almost) all done by GUI

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjICspLALGMgKxnMMgGyUsAsQySFSCqRlELVxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value = _t, name = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"value", Int64.Type}, {"name", type text}}),
        grp = Table.Group(#"Changed Type", {"name"}, {{"grp", each _[value]}}),
        #"Extracted Values" = Table.TransformColumns(grp, {"grp", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "grp", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"grp.1", "grp.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"grp.1", Int64.Type}, {"grp.2", Int64.Type}}),
        #"Transposed Table" = Table.Transpose(#"Changed Type1"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
        #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}})
    in
        #"Changed Type2"