amp-appInsights-advanced-editor-query.txt

let
    Source = AzureStorage.Blobs("AZURESTORAGENAME"),
    ampdata = Source{[Name="CONTAINERNAME"]}[Data],
    #"Combined Binaries" = Binary.Combine(ampdata[Content]),
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"Combined Binaries",null,null,1252)}),
    #"Parsed JSON" = Table.TransformColumns(#"Imported Text",{},Json.Document),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"event", "internal", "context"}, {"event", "internal", "context"}),
    #"Expanded event" = Table.ExpandListColumn(#"Expanded Column1", "event"),
    #"Expanded event1" = Table.ExpandRecordColumn(#"Expanded event", "event", {"name"}, {"name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded event1", each ([name] = "playbackSummary")),
    #"Expanded internal" = Table.ExpandRecordColumn(#"Filtered Rows", "internal", {"data"}, {"data"}),
    #"Expanded data" = Table.ExpandRecordColumn(#"Expanded internal", "data", {"id"}, {"id"}),
    #"Expanded context" = Table.ExpandRecordColumn(#"Expanded data", "context", {"device", "location", "data", "user", "custom"}, {"device", "location", "data", "user", "custom"}),
    #"Expanded device" = Table.ExpandRecordColumn(#"Expanded context", "device", {"osVersion", "browser", "browserVersion"}, {"osVersion", "browser", "browserVersion"}),
    #"Expanded location" = Table.ExpandRecordColumn(#"Expanded device", "location", {"continent", "country", "clientip", "province", "city"}, {"continent", "country", "clientip", "province", "city"}),
    #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded location", "data", {"isSynthetic", "samplingRate", "eventTime"}, {"isSynthetic", "samplingRate", "eventTimeStamp"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded data1", "eventTimeStamp", "eventDate"),
    #"Parsed Date" = Table.TransformColumns(#"Duplicated Column",{{"eventDate", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Parsed Date", "eventTimeStamp", "eventTime"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column1",{{"eventTime", type datetimezone}}),
    #"Expanded user" = Table.ExpandRecordColumn(#"Changed Type", "user", {"isAuthenticated", "anonId"}, {"isAuthenticated", "anonId"}),
    #"Expanded custom" = Table.ExpandRecordColumn(#"Expanded user", "custom", {"dimensions", "metrics"}, {"dimensions", "metrics"}),
    #"Expanded dimensions" = Table.ExpandListColumn(#"Expanded custom", "dimensions"),
    #"Expanded dimensions1" = Table.ExpandRecordColumn(#"Expanded dimensions", "dimensions", {"StreamId", "PluginVersion", "PlayerVersion", "PlaybackTech", "MimeType", "ProtectionType", "isLive"}, {"StreamId", "PluginVersion", "PlayerVersion", "PlaybackTech", "MimeType", "ProtectionType", "isLive"}),
    #"Expanded metrics1" = Table.ExpandListColumn(#"Expanded dimensions1", "metrics"),
    #"Expanded metrics2" = Table.ExpandRecordColumn(#"Expanded metrics1", "metrics", {"playTime", "fullscreenTime", "rebufferCount", "rebufferTime", "loadTime", "percentPlayed", "avgBitrate", "failedDownloads", "errorCode"}, {"playTime", "fullscreenTime", "rebufferCount", "rebufferTime", "loadTime", "percentPlayed", "avgBitrate", "failedDownloads", "errorCode"}),
    #"Expanded playTime" = Table.ExpandRecordColumn(#"Expanded metrics2", "playTime", {"value"}, {"playTime.value"}),
    #"Expanded fullscreenTime" = Table.ExpandRecordColumn(#"Expanded playTime", "fullscreenTime", {"value"}, {"fullscreenTime.value"}),
    #"Expanded rebufferCount" = Table.ExpandRecordColumn(#"Expanded fullscreenTime", "rebufferCount", {"value"}, {"rebufferCount.value"}),
    #"Expanded rebufferTime" = Table.ExpandRecordColumn(#"Expanded rebufferCount", "rebufferTime", {"value"}, {"rebufferTime.value"}),
    #"Divided Column" = Table.TransformColumns(#"Expanded rebufferTime", {{"rebufferTime.value", each _ / 1000, type number}}),
    #"Expanded loadTime1" = Table.ExpandRecordColumn(#"Divided Column", "loadTime", {"value"}, {"loadTime.value"}),
    #"Divided Column1" = Table.TransformColumns(#"Expanded loadTime1", {{"loadTime.value", each _ / 1000, type number}}),
    #"Expanded percentPlayed" = Table.ExpandRecordColumn(#"Divided Column1", "percentPlayed", {"value"}, {"percentPlayed.value"}),
    #"Expanded avgBitrate" = Table.ExpandRecordColumn(#"Expanded percentPlayed", "avgBitrate", {"value"}, {"avgBitrate.value"}),
    #"Expanded failedDownloads" = Table.ExpandRecordColumn(#"Expanded avgBitrate", "failedDownloads", {"value"}, {"failedDownloads.value"}),
    #"Expanded errorCode" = Table.ExpandRecordColumn(#"Expanded failedDownloads", "errorCode", {"value"}, {"errorCode.value"}),
    #"Grouped Rows" = Table.Group(#"Expanded errorCode", {"name", "id", "osVersion", "browser", "browserVersion", "continent", "country", "clientip", "province", "city", "isSynthetic", "samplingRate", "eventTimeStamp", "isAuthenticated", "anonId", "eventDate", "eventTime"}, {{"StreamId", each List.Max([StreamId]), type text}, {"PluginVersion", each List.Max([PluginVersion]), type none}, {"PlayerVersion", each List.Max([PlayerVersion]), type none}, {"PlaybackTech", each List.Max([PlaybackTech]), type none}, {"MimeType", each List.Max([MimeType]), type none}, {"ProtectionType", each List.Max([ProtectionType]), type none}, {"isLive", each List.Max([isLive]), type none}, {"playTime", each List.Max([playTime.value]), type number}, {"fullscreenTime", each List.Max([fullscreenTime.value]), type number}, {"rebufferCount", each List.Max([rebufferCount.value]), type number}, {"rebufferTime", each List.Max([rebufferTime.value]), type number}, {"loadTime", each List.Max([loadTime.value]), type number}, {"percentPlayed", each List.Max([percentPlayed.value]), type number}, {"avgBitrate", each List.Max([avgBitrate.value]), type number}, {"failedDownloads", each List.Max([failedDownloads.value]), type number}, {"errorCode", each List.Max([errorCode.value]), type none}})
in
    #"Grouped Rows"