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 hardcodingBut 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"}
