Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Is there any way to extract data, even if it is not in correct JSON format. I am coming up to this point:
Hi @Sab ,
Maybe not the best code, but try it:
let
Source = Json.Document(File.Contents("D:\Downloads\example.json")),
#"Added Custom" = Json.Document(Source),
#"Converted to Table" = Record.ToTable(#"Added Custom"),
#"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"),
Header = List.Transform(#"Pivoted Column"[column_definitions]{0}, each Record.FieldNames(_){0}),
Step1 = Table.FromList(List.Transform(#"Pivoted Column"[row_values]{0},
each
Table.Transpose(Table.FromList(_, Splitter.SplitByNothing(), null, null, ExtraValues.Error))
), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" =
Table.Combine({
Table.Transpose(Table.FromList(Header, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
Table.ExpandTableColumn(Step1, "Column1", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Column1", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Database_RecID", Int64.Type}, {"Index_Number", Int64.Type}, {"Support_Type", type text}})
in
#"Changed Type"
Thanks a lot! I am trying to understand Header and Step1 steps, I guess there is no commands to do those steps and you have to manually write the code?!
@Sab ,
Yes, I thought easier to write the code, it's less steps than UI steps (I think).
I just navigated through the lists and transformed it to tables.
Can you do it with UI steps please?
Thanks!
@Sab ,
This one with headers.
let
Source = Json.Document(File.Contents("D:\Downloads\example.json")),
#"Added Custom" = Json.Document(Source),
#"Converted to Table" = Record.ToTable(#"Added Custom"),
#"Added Custom1" = Table.AddColumn(#"Converted to Table", "Custom", each if [Name] = "column_definitions" then
Table.Transpose(Table.FromList(List.Transform([Value], each Record.FieldNames(_){0}), Splitter.SplitByNothing(), null, null, ExtraValues.Error))
else
Table.Combine(List.Transform([Value]
, each Table.Transpose(Table.FromList(_, Splitter.SplitByNothing(), null, null, ExtraValues.Error))))),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Database_RecID", Int64.Type}, {"Index_Number", Int64.Type}, {"Support_Type", type text}})
in
#"Changed Type"
I've to input code in custom columns, I don't know how to do it without coding.
Thanks a lot again, much appreciated!
@Sab ,
let
Source = Json.Document(File.Contents("D:\Downloads\example.json")),
#"Added Custom" = Json.Document(Source),
row_values = #"Added Custom"[row_values],
#"Converted to Table" = Table.FromList(row_values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}, {"Column1.3", type text}})
in
#"Changed Type"
Without the headers.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |