We found extra characters at the end of the JSON input

jwkuper_eof — Mon, 02 Oct 2023 10:00:00 GMT

In an Azure blobstorage there is data with the M365 sign-in logs.

When I connect to this data from a Fabric data flow, I can connect to the data and open it in Power Query. Filtering of the data of last month is showing the correct rows. After expanding the column [Content] an error is shown:
DataFormat.Error: We found extra characters at the end of the JSON input.

Details

Value = {

Position = 4407

section Section1;

shared #"insights-logs-signinlogs" = let

Source = AzureStorage.Blobs("https://xxx.blob.core.windows.net"),

Navigation = Source{[Name = "insights-logs-signinlogs"]}[Data],

#"Filtered rows" = Table.SelectRows(Navigation, each [Date modified] >= #datetime(2023, 9, 1, 0, 0, 0) and [Date modified] < #datetime(2023, 10, 1, 0, 0, 0)),

#"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),

#"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content])),

#"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),

#"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file"}),

#"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file")))

#"Expanded table column";

shared #"Sample file" = let

Source = AzureStorage.Blobs("https://powerautomatedemo.blob.core.windows.net"),

Navigation = Source{[Name = "insights-logs-signinlogs"]}[Data],

#"Filtered rows" = Table.SelectRows(Navigation, each [Date modified] >= #datetime(2023, 9, 1, 0, 0, 0) and [Date modified] < #datetime(2023, 10, 1, 0, 0, 0)),

#"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),

#"Navigation 1" = #"Filtered hidden files"{0}[Content]

#"Navigation 1";

shared Parameter = let

Parameter = #"Sample file" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type binary, BinaryIdentifier = #"Sample file"]

Parameter;

shared #"Transform Sample file" = let

Source = Json.Document(Parameter)

Source;

[FunctionQueryBinding = "{""exemplarFormulaName"":""Transform Sample file""}"]

shared #"Transform file" = (Parameter as binary) => let

Source = Json.Document(Parameter)

Source;

The steps in the Advanced editor:
let

Source = AzureStorage.Blobs("https://xxx.blob.core.windows.net"),

Navigation = Source{[Name = "insights-logs-signinlogs"]}[Data],

#"Filtered rows" = Table.SelectRows(Navigation, each [Date modified] >= #datetime(2023, 9, 1, 0, 0, 0) and [Date modified] < #datetime(2023, 10, 1, 0, 0, 0)),

#"Filtered hidden files" = Table.SelectRows(#"Filtered rows", each [Attributes]?[Hidden]? <> true),

#"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content])),

#"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),

#"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file"}),

#"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file")))

#"Expanded table column"

When I connect to this data from Power BI Desktop I can connect to the data and have no error message and the columns are visible.

Advanced editor in Power BI Desktop
let

Source = AzureStorage.Blobs("https://xxx.blob.core.windows.net"),

#"insights-logs-signinlogs1" = Source{[Name="insights-logs-signinlogs"]}[Data],

#"Filtered Rows" = Table.SelectRows(#"insights-logs-signinlogs1", each [Date modified] >= #datetime(2023, 9, 1, 0, 0, 0) and [Date modified] < #datetime(2023, 10, 1, 0, 0, 0)),

#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),

#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),

#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),

#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"time", type datetime}, {"resourceId", type text}, {"operationName", type text}, {"operationVersion", Int64.Type}, {"category", type text}, {"tenantId", type text}, {"resultType", Int64.Type}, {"resultSignature", type text}, {"durationMs", Int64.Type}, {"callerIpAddress", type any}, {"correlationId", type text}, {"identity", type text}, {"Level", Int64.Type}, {"location", type text}, {"properties.id", type text}, {"properties.createdDateTime", type datetime}, {"properties.userDisplayName", type text}, {"properties.userPrincipalName", type text}, {"properties.userId", type text}, {"properties.appId", type text}, {"properties.appDisplayName", type text}, {"properties.ipAddress", type any}, {"properties.status.errorCode", Int64.Type}, {"properties.status.additionalDetails", type text}, {"properties.clientAppUsed", type text}, {"properties.userAgent", type text}, {"properties.deviceDetail.deviceId", type text}, {"properties.deviceDetail.displayName", type text}, {"properties.deviceDetail.operatingSystem", type text}, {"properties.deviceDetail.browser", type text}, {"properties.deviceDetail.isCompliant", type logical}, {"properties.deviceDetail.isManaged", type logical}, {"properties.deviceDetail.trustType", type text}, {"properties.location.city", type text}, {"properties.location.state", type text}, {"properties.location.countryOrRegion", type text}, {"properties.location.geoCoordinates.latitude", type number}, {"properties.location.geoCoordinates.longitude", type number}, {"properties.correlationId", type text}, {"properties.conditionalAccessStatus", type text}, {"properties.appliedConditionalAccessPolicies", type any}, {"properties.authenticationContextClassReferences", type any}, {"properties.originalRequestId", type text}, {"properties.isInteractive", type logical}, {"properties.tokenIssuerName", type any}, {"properties.tokenIssuerType", type text}, {"properties.authenticationProcessingDetails", type any}, {"properties.networkLocationDetails", type any}, {"properties.clientCredentialType", type text}, {"properties.processingTimeInMilliseconds", Int64.Type}, {"properties.riskDetail", type text}, {"properties.riskLevelAggregated", type text}, {"properties.riskLevelDuringSignIn", type text}, {"properties.riskState", type text}, {"properties.riskEventTypes", type any}, {"properties.riskEventTypes_v2", type any}, {"properties.resourceDisplayName", type text}, {"properties.resourceId", type text}, {"properties.resourceTenantId", type text}, {"properties.homeTenantId", type text}, {"properties.tenantId", type text}, {"properties.authenticationDetails", type any}, {"properties.authenticationRequirementPolicies", type any}, {"properties.sessionLifetimePolicies", type any}, {"properties.authenticationRequirement", type text}, {"properties.servicePrincipalId", type any}, {"properties.userType", type text}, {"properties.flaggedForReview", type logical}, {"properties.isTenantRestricted", type logical}, {"properties.autonomousSystemNumber", Int64.Type}, {"properties.crossTenantAccessType", type text}, {"properties.ssoExtensionVersion", type any}, {"properties.uniqueTokenIdentifier", type text}, {"properties.authenticationStrengths", type any}, {"properties.incomingTokenType", type text}, {"properties.authenticationProtocol", type text}, {"properties.appServicePrincipalId", type any}, {"properties.resourceServicePrincipalId", type text}, {"properties.rngcStatus", Int64.Type}})

#"Changed Type"

How can I use the source in a data flow without having the error?

Re: We found extra characters at the end of the JSON input

miguel — Mon, 02 Oct 2023 16:16:07 GMT

when you use Power BI Desktop and you try to evaluate the full query and load it to the data model, do you get any errors?

There could be inconsistencies between how the data preview gets evaluated in Power BI Desktop and dataflows. They dont use the same mechanism behind the scenes and in most cases the Power Query Online experience (Through dataflows) can help you catch situations like this before your query gets fully evaluated.

If not, and you get this inconsistency in full evaluations between both environments (beyond just the data preview in the Power Query Editor) then please share a sample json file or set of files that could help us replicate the issue. Alternatively, you can also go ahead and raise a support ticket so that an engineer can take a closer look at it.

https://support.fabric.microsoft.com/support

Re: We found extra characters at the end of the JSON input

v-cboorla-msft — Tue, 10 Oct 2023 07:04:02 GMT

Hi jwkuper_eof

Thanks for using Fabric Community. I would like to check whether your issue got resolved or got a chance to create a support ticket.
If so, it would be a great help if you can provide the details of the Support Ticket so we can track it for further information.

topic Re: We found extra characters at the end of the JSON input in Dataflow

We found extra characters at the end of the JSON input

Re: We found extra characters at the end of the JSON input

Re: We found extra characters at the end of the JSON input