Forum Discussion
Anonymous
6 years agoNot applicable
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 ...
- Anonymous6 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"
Icey
Community Support
6 years agoHi Anonymous ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know.
Best Regards,
Icey