Monitor and optimize on-premises data gateway performance - Microsoft createdpbix has missing fields

Hi stty1244 

 

Here are some steps for your reference:

Ensure that the 'ReportFilePath' in the configuration file matches the path where your log files are stored.

 

As part of the M-query, the Query "ServiceTraceContexts", which tries to create an new field named QueryType . As there is already a field named QueryType, This operation fails, so you can update the M-query, modify the 'QueryType' header to 'QueryTypes' in the M-query:

let
    Source = Folder.Files(#"Folder Path"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "QueryStartReport") and [Extension] = ".log"),
    #"CsvDoc" = Table.AddColumn(#"Filtered Rows", "csvDocColumn", each try Table.PromoteHeaders(Csv.Document([Content], {"GatewayObjectId", "RequestId", "DataSource", "QueryTrackingId", "QueryExecutionStartTimeUTC", "QueryType", "QueryText", "EvaluationContext"}))),
    #"Expanded csvDocColumn" = Table.ExpandRecordColumn(CsvDoc, "csvDocColumn", {"HasError", "Value"}, {"csvDocColumn.HasError", "csvDocColumn.Value"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded csvDocColumn", each [csvDocColumn.HasError] = false),
    #"Expanded csvDocColumn.Value" = Table.ExpandTableColumn(#"Filtered Rows1", "csvDocColumn.Value", {"GatewayObjectId", "RequestId", "DataSource", "QueryTrackingId", "QueryExecutionStartTimeUTC", "QueryType", "QueryText", "EvaluationContext"}, {"GatewayObjectId", "RequestId", "DataSource", "QueryTrackingId", "QueryExecutionStartTimeUTC", "QueryType", "QueryText", "EvaluationContext"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded csvDocColumn.Value", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path", "csvDocColumn.HasError"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Name", "Source.Name"}}),
   #"JsonDoc" = Table.AddColumn(#"Renamed Columns", "jsonDocColumn", each try Json.Document([DataSource])),
    #"Expanded jsonDocColumn" = Table.ExpandRecordColumn(JsonDoc, "jsonDocColumn", {"HasError", "Value"}, {"HasError", "Value"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded jsonDocColumn", each [HasError] = false),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows2",{"DataSource"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Value", "DataSource"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"HasError"}),
    #"Expanded DataSource" = Table.ExpandListColumn(#"Removed Columns2", "DataSource"),
    #"Parsed JSON1" = Table.TransformColumns(#"Expanded DataSource",{{"DataSource", Json.Document}}),
    #"Expanded DataSource1 - kind" = Table.AddColumn(#"Parsed JSON1", "DataSource.kind", each Record.FieldOrDefault([DataSource], "kind") ?? Record.FieldOrDefault([DataSource], "ConnectorType")),
    #"Expanded DataSource1 - path" = Table.AddColumn(#"Expanded DataSource1 - kind", "DataSource.path", each Record.FieldOrDefault([DataSource], "path") ?? Text.Combine(List.RemoveFirstN(Record.FieldValues([DataSource]), 1), ";")),
    #"Added Custom" = Table.AddColumn(#"Expanded DataSource1 - path", "QueryText1", each Binary.FromText([QueryText], BinaryEncoding.Base64)),
    #"Transformed Column" = Table.TransformColumns(#"Added Custom",{{"QueryText1", Text.FromBinary}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Transformed Column",{"QueryText"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns3",{{"QueryText1", "QueryText"}}),
     #"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{{"QueryType", "QueryTypes"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns3", {"Index", "GatewayObjectId", "Source.Name", "RequestId", "QueryTrackingId", "QueryExecutionStartTimeUTC",  "QueryTypes", "QueryText", "EvaluationContext"}, {{"Count", each Table.RowCount(_), type number}, {"DataSourceType", each Text.Combine([DataSource.kind],","), type text}, {"DataSource", each Text.Combine([DataSource.path],","), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Source.Name", type text}, {"GatewayObjectId", type text}, {"RequestId", type text}, {"DataSource", type text}, {"QueryTrackingId", type text}, {"QueryExecutionStartTimeUTC", type datetime}, {"QueryTypes", type text}, {"QueryText", type text}}, "en-us"),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"QueryTrackingId"}, QueryExecutionReport, {"QueryTrackingId"}, "QueryExecutionReport", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
    #"Expanded QueryExecutionReport" = Table.ExpandTableColumn(#"Merged Queries", "QueryExecutionReport", {"QueryExecutionEndTimeUTC", "DataReadingAndSerializationDuration(ms)", "SpoolingDiskWritingDuration(ms)", "SpoolingDiskReadingDuration(ms)", "SpoolingTotalDataSize(byte)", "DataProcessingEndTimeUTC", "DataProcessingDuration(ms)", "Success", "ErrorMessage"}, {"QueryExecutionEndTimeUTC", "DataReadingAndSerializationDuration(ms)", "SpoolingDiskWritingDuration(ms)", "SpoolingDiskReadingDuration(ms)", "SpoolingTotalDataSize(byte)", "DataProcessingEndTimeUTC", "DataProcessingDuration(ms)", "Success", "ErrorMessage"}),
    #"Replaced Value" = let
#"step1"=Table.ReplaceValue(#"Expanded QueryExecutionReport","null","{""serviceTraceContexts"": [{""serviceName"": ""null"",""traceIds"": [{""key"": ""NoKey"",""value"": ""null""}]}]}",Replacer.ReplaceValue,{"EvaluationContext"}),
#"step2"=Table.ReplaceValue(#"step1",null,"{""serviceTraceContexts"": [{""serviceName"": ""null"",""traceIds"": [{""key"": ""NoKey"",""value"": ""null""}]}]}",Replacer.ReplaceValue,{"EvaluationContext"})
in #"step2",
    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value",{{"EvaluationContext", Json.Document}}),
    #"Expanded EvaluationContext" = Table.ExpandRecordColumn(#"Parsed JSON", "EvaluationContext", {"serviceTraceContexts"}, {"EvaluationContext.serviceTraceContexts"}),
    #"Expanded EvaluationContext.serviceTraceContexts" = Table.ExpandListColumn(#"Expanded EvaluationContext", "EvaluationContext.serviceTraceContexts"),
    #"Expanded EvaluationContext.serviceTraceContexts1" = Table.ExpandRecordColumn(#"Expanded EvaluationContext.serviceTraceContexts", "EvaluationContext.serviceTraceContexts", {"serviceName", "traceIds"}, {"EvaluationContext.serviceTraceContexts.serviceName", "EvaluationContext.serviceTraceContexts.traceIds"}),
    #"Expanded EvaluationContext.serviceTraceContexts.traceIds" = Table.ExpandListColumn(#"Expanded EvaluationContext.serviceTraceContexts1", "EvaluationContext.serviceTraceContexts.traceIds"),
    #"Expanded EvaluationContext.serviceTraceContexts.traceIds1" = Table.ExpandRecordColumn(#"Expanded EvaluationContext.serviceTraceContexts.traceIds", "EvaluationContext.serviceTraceContexts.traceIds", {"key", "value"}, {"EvaluationContext.serviceTraceContexts.traceIds.key", "EvaluationContext.serviceTraceContexts.traceIds.value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded EvaluationContext.serviceTraceContexts.traceIds1", List.Distinct(#"Expanded EvaluationContext.serviceTraceContexts.traceIds1"[EvaluationContext.serviceTraceContexts.traceIds.key]), "EvaluationContext.serviceTraceContexts.traceIds.key", "EvaluationContext.serviceTraceContexts.traceIds.value"),
    #"Renamed Columns4" = Table.RenameColumns(#"Pivoted Column",{{"EvaluationContext.serviceTraceContexts.serviceName", "ServiceName"}})
    //#"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns4",{"NoKey"})
in
    #"Renamed Columns4"

 

 

Hope this can help,

 

 

Best Regards

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