Properly load a JSON file from a folder as source

[CR]

Hi guys,

I know how to load a JSON file ifself

let
    Source = Json.Document(File.Contents("\\...\refreshables.json")),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{2}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "name", "kind", "lastRefresh", "refreshSchedule", "configuredBy", "capacity", "group", "startTime", "endTime", "refreshCount", "refreshFailures", "averageDuration", "medianDuration", "refreshesPerDay"}, {"id", "name", "kind", "lastRefresh", "refreshSchedule", "configuredBy", "capacity", "group", "startTime", "endTime", "refreshCount", "refreshFailures", "averageDuration", "medianDuration", "refreshesPerDay"}),
    #"Expanded lastRefresh" = Table.ExpandRecordColumn(#"Expanded Column1", "lastRefresh", {"id", "refreshType", "startTime", "endTime", "serviceExceptionJson", "status", "requestId"}, {"lastRefresh.id", "lastRefresh.refreshType", "lastRefresh.startTime", "lastRefresh.endTime", "lastRefresh.serviceExceptionJson", "lastRefresh.status", "lastRefresh.requestId"}),
    #"Expanded refreshSchedule" = Table.ExpandRecordColumn(#"Expanded lastRefresh", "refreshSchedule", {"days", "times", "enabled", "localTimeZoneId", "notifyOption"}, {"refreshSchedule.days", "refreshSchedule.times", "refreshSchedule.enabled", "refreshSchedule.localTimeZoneId", "refreshSchedule.notifyOption"}),
    #"Expanded refreshSchedule.days" = Table.ExpandListColumn(#"Expanded refreshSchedule", "refreshSchedule.days"),
    #"Expanded refreshSchedule.times" = Table.ExpandListColumn(#"Expanded refreshSchedule.days", "refreshSchedule.times"),
    #"Expanded configuredBy" = Table.ExpandListColumn(#"Expanded refreshSchedule.times", "configuredBy"),
    #"Expanded capacity" = Table.ExpandRecordColumn(#"Expanded configuredBy", "capacity", {"id", "displayName", "sku"}, {"capacity.id", "capacity.displayName", "capacity.sku"}),
    #"Expanded group" = Table.ExpandRecordColumn(#"Expanded capacity", "group", {"id", "name"}, {"group.id", "group.name"}),
...

But I don't know how to do when the JSON file is one a the files of a folder I get as the source. 

let
    Source = PBIAuditLogRaw,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Source.Name] = "refreshables.json"))
     ???
in

Any idea of what I have to put instead of the ??? ?

Regards,

CR 

[v-lionel-msft]

Hi @CR ,

Is your JSON file located in a certain folder?

Do you want to use the 'Folder' connector to import and merge the files of the entire folder?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CR]

Hi @v-lionel-msft ,

This is indeed  folder and containing about 20 csv files (no issue with them) and one json file (the one I'm talking about).

The load is fine now, it's just that I don't know how to convert the content to a clean table (as explained, I hope, in my last post).

Regards,

Camille

[lbendlin]

You're missing the step where you parse the JSON.  (You just imported it as a CSV).

"I don't know how to convert the content to a clean table"

That's not how JSON works.  It represents a hierarchy.  A table has no hierarchy.

You can flatten the JSON in some cases to make it look like a (very inefficent) table, but in most cases you can only grab certain attributes from your JSON for further processing.

[mahoneypat]

From that step, just click on the "Binary" hyperlink (or whatever hyperlink is in the [Content] column (or something like that).  That will create a Navigate step and it should look familiar after that.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[CR]

@mahoneypat , @lbendlin ,

Thanks. I expand all the columns and I got 48 columns.

let
    Source = PBIAuditLogRaw,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Source.Name] = "refreshables.json")),
    #"Expanded Transform File from PBIAuditLog" = Table.ExpandTableColumn(#"Filtered Rows", "Transform File from PBIAuditLog", {"{", "", "_1", "_2", "_3", "_4", "_5", "_6", "_7", "_8", "_9", "_10", "_11", "_12", "_13", "_14", "_15", "_16", "_17", "_18", "_19", "_20", "_21", "_22", "_23", "_24", "_25", "_26", "_27", "_28", "_29", "_30", "_31", "_32", "_33", "_34", "_35", "_36", "_37", "_38", "_39", "_40", "_41", "_42", "_43", "_44", "_45", "_46", "_47", "_48"}, {"{", "Column1", "_1", "_2", "_3", "_4", "_5", "_6", "_7", "_8", "_9", "_10", "_11", "_12", "_13", "_14", "_15", "_16", "_17", "_18", "_19", "_20", "_21", "_22", "_23", "_24", "_25", "_26", "_27", "_28", "_29", "_30", "_31", "_32", "_33", "_34", "_35", "_36", "_37", "_38", "_39", "_40", "_41", "_42", "_43", "_44", "_45", "_46", "_47", "_48"})
in
    #"Transform File from PBIAuditLog"

I also get the following table for thousand of ID's. Here is a sample of the first ID (limited to 13 columns instead 48).

Source.Name

{

Column1

_1

_2

_3

_4

_5

_6

_7

_8

_9

_10

_11

refreshables.json

"@odata.context":"http://.../$metadata#refreshables"

@odata.count:332

value:[

refreshables.json

{

refreshables.json

"id":"XXXX1"

name:"Name1"

kind:"Dataset"

startTime:"2020-06-13T23:01:18.85Z"

endTime:"2020-06-17T01:38:02.4Z"

refreshCount:20

refreshFailures:4

averageDuration:3989.5736666666667

medianDuration:3111.936

refreshesPerDay:6

lastRefresh:{

refreshables.json

"id":33283100

refreshType:"Scheduled"

startTime:"2020-06-17T03:30:08.257Z"

endTime:null

serviceExceptionJson:null

status:"Unknown"

requestId:"..."

refreshables.json

}

refreshSchedule:{

refreshables.json

"days":[

refreshables.json

"Sunday"

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

refreshables.json

]

times:[

refreshables.json

"01:00"

05:30

09:00

13:00

17:00

22:00

refreshables.json

]

enabled:true

localTimeZoneId:"Romance Standard Time"

notifyOption:"MailOnFailure"

refreshables.json

}

configuredBy:[

refreshables.json

"email"

refreshables.json

]

capacity:{

refreshables.json

"id":"capa1"

displayName:"capa-name"

sku:"P1"

refreshables.json

}

group:{

refreshables.json

"id":"group1"

name:"group1"

refreshables.json

}

refreshables.json

}

{

The issue is I don't see how to get a clean table by getting all information for one ID in one line.

I tried to convert to a list but it seems this is more complex.

Regards,

CR

[mahoneypat · Accepted solution]

Your first two steps look correct, but the next should be a navigate step.  Do you see something like this at that step?

If so, click on the word "Binary" which should open your file as a JSON type, and then you can use similar steps as your first query from there.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[CR]

 @mahoneypat 

The content was not Binary but Table. I also start again the upload and, now, it works perfectly fine. So, the issue is solved but I don't know why because I'm not able to produce again the issue.

Bye,

CR

[lbendlin]

what folder, file folder or Sharepoitn folder?

In any case, just open a connection to your folder source, then set  a filter for the target path, file extension and file name as needed, and then continue with your JSON parse code from there. Check the generated Power Query code, it is pretty straightforward.