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"
Jimmy801
Community Champion
6 years agoHello Anonymous
try to add a new step after your step of ExpandedList (first screenshot) like this:
Table.FromRecords(YourStepOfExpandedList[Column1)
Hope it helps
Jimmy