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")))
in
#"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]
in
#"Navigation 1";
shared Parameter = let
Parameter = #"Sample file" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type binary, BinaryIdentifier = #"Sample file"]
in
Parameter;
shared #"Transform Sample file" = let
Source = Json.Document(Parameter)
in
Source;
[FunctionQueryBinding = "{""exemplarFormulaName"":""Transform Sample file""}"]
shared #"Transform file" = (Parameter as binary) => let
Source = Json.Document(Parameter)
in
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")))
in
#"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}})
in
#"Changed Type"
How can I use the source in a data flow without having the error?
