Forum Discussion
JSON data from cell to Columns
Hello Everyone, I have a column in my excel File which has a simple non-nested data. I need to trasferthis data in columns , where variable name is column name and variable value is cell value. ...
You may do that with Power Query - query you table with JSON texts, parse column as JSON and expand result.
Generated script looks like
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Parsed JSON" = Table.TransformColumns(
Source,
{},Json.Document
),
#"Expanded All Specification" = Table.ExpandRecordColumn(
#"Parsed JSON",
"All Specification",
{"Movement", "Water Resistance Depth", "watch men", "Boxes & Cases Material",
"Item Type", "relogio masculino", "Model Number", "Feature", "Case Thickness",
"Brand Name", "Band Length", "Case Shape", "Dial Diameter", "reloj hombre",
"Band Width", "Band Material Type", "montre homme", "Style",
"Dial Window Material Type", "Clasp Type", "Case Material", "Occasion",
"Drop Shipping", "Gender", "mens designer watches luxury watch",
"montre homme marque de luxe", "Top Selling Watch", "Application"},
{"Movement", "Water Resistance Depth", "watch men", "Boxes & Cases Material",
"Item Type", "relogio masculino", "Model Number", "Feature", "Case Thickness",
"Brand Name", "Band Length", "Case Shape", "Dial Diameter", "reloj hombre",
"Band Width", "Band Material Type", "montre homme", "Style",
"Dial Window Material Type", "Clasp Type", "Case Material", "Occasion",
"Drop Shipping", "Gender", "mens designer watches luxury watch",
"montre homme marque de luxe", "Top Selling Watch", "Application"}
)
in
#"Expanded All Specification"Please check in attached.
First of all , thanks for replying.
The query that you gave has names of "variables" in jsons. I have a huge data, how can i dentify unique variables in that data.
Can't i get some code which automatically makes "variable" names as column names and values in cells. without any hardcoding
But here nothing is hardcoded, query takes columns names automatically from JSON file.
SergeiBaklan
Expanded All selection step in query has column names"All Specification", {"Movement", "Water Resistance Depth", "watch men", "Boxes & Cases Material", "Item Type", "relogio masculino", "Model Number", "Feature", "Case Thickness", "Brand Name", "Band Length", "Case Shape", "Dial Diameter", "reloj hombre", "Band Width", "Band Material Type", "montre homme", "Style", "Dial Window Material Type", "Clasp Type", "Case Material", "Occasion", "Drop Shipping", "Gender", "mens designer watches luxury watch", "montre homme marque de luxe", "Top Selling Watch", "Application"}, {"Movement", "Water Resistance Depth", "watch men", "Boxes & Cases Material", "Item Type", "relogio masculino", "Model Number", "Feature", "Case Thickness", "Brand Name", "Band Length", "Case Shape", "Dial Diameter", "reloj hombre", "Band Width", "Band Material Type", "montre homme", "Style", "Dial Window Material Type", "Clasp Type", "Case Material", "Occasion", "Drop Shipping", "Gender", "mens designer watches luxury watch", "montre homme marque de luxe", "Top Selling Watch", "Application"}Actually much more if don't skip Load more on expanding. All variables in your sample are
"All Specification", {"Movement", "Water Resistance Depth", "watch men", "Boxes & Cases Material", "Item Type","relogio masculino", "Model Number", "Feature", "Case Thickness", "Brand Name", "Band Length","Case Shape", "Dial Diameter", "reloj hombre", "Band Width", "Band Material Type", "montre homme","Style", "Dial Window Material Type", "Clasp Type", "Case Material", "Occasion", "Drop Shipping", "Gender", "mens designer watches luxury watch", "montre homme marque de luxe", "Top Selling Watch", "Application", "DOOBO Watch Men", "Relogio Masculino", "Quartz-Watch Men", "Quartz Wristwatches", "Fashion Casual Watch", "Mens Watch", "Fuction7", "Fuction6", "Fuction5", "Fuction4", "Fuction3","Fuction2", "Fuction1", "Fuction9", "Fuction8", "Fuction", "Gender1", "Turkish", "Design","Portuguese", "Item type", "Watch Name", "French Name", "Spanish Name", "Watch Men's", "Men Watch", "Quartz-watch", "Men's Watches", "Relogios Masculino", "Watches Style", "Sports Watchs", "Fashion Casual Watch ", "Relogio Masculino ", " watch women", "Dress Lady Watch", "watch electronic", "womens wristwatch ", "digital watch", "color", "sports watches", "men's watches", "ultra thin watch", "watches", "relojes", "erkek saat", "relogios masculino","mens watches top brand luxury", "orologio uomo", "erkek kol saati", "montre femme","simple watch for men", "women watch", "quartz watch", "creative watches men","casual watch men", "men watch", "Spain Words", "One", "France Words", "For the Crowd","Brazil Words", "Condition", "Color", "feature", "Brazil word", "case material", "crowd","Spain word", "diameter", "model", "relogio feminino", "item", "length", "men clock","width", "style", "reloj mujer", "band material", "Army", "2018 New Casual Watch Quartz Watches", "New Arrival Girls Ladies Women's Watch", "Analog", "Brazil words", "Montre Homme: Montre Homme De Marque", "Display"}, {"Movement", "Water Resistance Depth", "watch men", "Boxes & Cases Material", "Item Type","relogio masculino", "Model Number", "Feature", "Case Thickness", "Brand Name", "Band Length","Case Shape", "Dial Diameter", "reloj hombre", "Band Width", "Band Material Type", "montre homme","Style", "Dial Window Material Type", "Clasp Type", "Case Material", "Occasion", "Drop Shipping", "Gender", "mens designer watches luxury watch", "montre homme marque de luxe", "Top Selling Watch", "Application", "DOOBO Watch Men", "Relogio Masculino", "Quartz-Watch Men", "Quartz Wristwatches", "Fashion Casual Watch", "Mens Watch", "Fuction7", "Fuction6", "Fuction5", "Fuction4", "Fuction3","Fuction2", "Fuction1", "Fuction9", "Fuction8", "Fuction", "Gender1", "Turkish", "Design","Portuguese", "Item type", "Watch Name", "French Name", "Spanish Name", "Watch Men's", "Men Watch", "Quartz-watch", "Men's Watches", "Relogios Masculino", "Watches Style", "Sports Watchs", "Fashion Casual Watch ", "Relogio Masculino ", " watch women", "Dress Lady Watch", "watch electronic", "womens wristwatch ", "digital watch", "color", "sports watches", "men's watches", "ultra thin watch", "watches", "relojes", "erkek saat", "relogios masculino","mens watches top brand luxury", "orologio uomo", "erkek kol saati", "montre femme","simple watch for men", "women watch", "quartz watch", "creative watches men","casual watch men", "men watch", "Spain Words", "One", "France Words", "For the Crowd","Brazil Words", "Condition", "Color", "feature", "Brazil word", "case material", "crowd","Spain word", "diameter", "model", "relogio feminino", "item", "length", "men clock","width", "style", "reloj mujer", "band material", "Army", "2018 New Casual Watch Quartz Watches", "New Arrival Girls Ladies Women's Watch", "Analog", "Brazil words", "Montre Homme: Montre Homme De Marque", "Display"}but that's how you JSON file is built. Power Query takes all variables automatically. In UI there are only three steps which are not required any coding.
Sorry, but I didn't catch what do you expect from the transformation.