Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sab
Helper V
Helper V

Properly load JSON file

Hello,

 

I need this json file to properly load into Power Query

 

Details 

 

Thank you so much!

9 REPLIES 9
Greg_Deckler
Super User
Super User

That is not valid JSON so it will not process. https://jsonformatter.curiousconcept.com/

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Is there any way to extract data, even if it is not in correct JSON format. I am coming up to this point:Untitled - Power Query Editor.jpg

camargos88
Community Champion
Community Champion

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"

 

Capture.PNG

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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?!

camargos88
Community Champion
Community Champion

@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.

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Can you do it with UI steps please?

 

Thanks!

camargos88
Community Champion
Community Champion

@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.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Thanks a lot again, much appreciated!

camargos88
Community Champion
Community Champion

@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.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.