Forum Discussion
Extracting data from JSON format column in power BI
Hi all,
I have a json format column in my table , in that whole rows are in the form of
{"v":[{"v":{"f":[{"v":"en"},{"v":" Sales are high this week"}]}},{"v":{"f":[{"v":"it"},{"v":sales are at peak range""}]}}]}
I need to extract Sales are high this week from above json format
Please help!
Thanks in advance
Hi Anonymous,
Here is the expanded table from the sample JSON string with custom query steps, you can try it if suitable for your requirement.
Full query:
let Source = Json.Document(File.Contents("C:\Users\xxxxx\Desktop\test.json")), #"Converted to Table" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"), #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"v"}, {"v"}), #"Expanded v" = Table.ExpandRecordColumn(#"Expanded Value1", "v", {"f"}, {"v.f"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded v", {"Name"}, "Node", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each Table.Transpose(Table.FromList(List.Transform([Value],each Record.Field(_,"v"))))), #"Expanded Expand" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Group", "Sales"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Expand",{"Value"}) in #"Removed Columns"Regards,
Xiaoxin Sheng
1 Reply
Hi Anonymous,
Here is the expanded table from the sample JSON string with custom query steps, you can try it if suitable for your requirement.
Full query:
let Source = Json.Document(File.Contents("C:\Users\xxxxx\Desktop\test.json")), #"Converted to Table" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"), #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"v"}, {"v"}), #"Expanded v" = Table.ExpandRecordColumn(#"Expanded Value1", "v", {"f"}, {"v.f"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded v", {"Name"}, "Node", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each Table.Transpose(Table.FromList(List.Transform([Value],each Record.Field(_,"v"))))), #"Expanded Expand" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Group", "Sales"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Expand",{"Value"}) in #"Removed Columns"Regards,
Xiaoxin Sheng