Forum Discussion
Properly load a JSON file from a folder as source
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
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
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
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