Resolver II

6 years ago

Solved

Properly load a JSON file from a folder as source

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,

Need Help

mahoneypat
6 years ago
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

8 Replies

v-lionel-msft
Community Support
6 years ago
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
  Resolver II
  6 years ago
  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
    Super User
    6 years ago
    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.
lbendlin
Super User
6 years ago
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.

mahoneypat

Microsoft Employee

6 years ago

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

Resolver II

6 years ago

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

_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,

mahoneypat
Microsoft Employee
6 years ago
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

Forum Discussion

Properly load a JSON file from a folder as source

8 Replies

Related content

OneDrive Folder as source

Data source folder

Folder Permissions

Change Source from 'Folder' to 'SharePoint Folder'

Replacing data sources to share point folder and file