Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Power Query function to extract all GET requests to ADLSgen2 storage
(SourceQuery as table) =>
let
Source = SourceQuery,
#"Filtered Rows" = Table.SelectRows(Source, each ([Data Source Kind] = "AzureDataLakeStorage")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Id", "Query", "Step", "Start Time", "End Time", "Exclusive Duration", "Resource", "Data Source Query", "Path", "Partition Key"}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Removed Other Columns", "Subtraction", each [End Time] - [Start Time], type duration),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "Actual Duration"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Data Source Query", Splitter.SplitTextByDelimiter("#(cr)", QuoteStyle.Csv), {"Data Source Query.1", "Data Source Query.2", "Data Source Query.3", "Data Source Query.4", "Data Source Query.5", "Data Source Query.6", "Data Source Query.7", "Data Source Query.8", "Data Source Query.9", "Data Source Query.10", "Data Source Query.11", "Data Source Query.12"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data Source Query.1", type text}, {"Data Source Query.2", type text}, {"Data Source Query.3", type text}, {"Data Source Query.4", type text}, {"Data Source Query.5", type text}, {"Data Source Query.6", type text}, {"Data Source Query.7", type text}, {"Data Source Query.8", type text}, {"Data Source Query.9", type text}, {"Data Source Query.10", type text}, {"Data Source Query.11", type text}, {"Data Source Query.12", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Data Source Query.1", "Data Source Query.3", "Data Source Query.4", "Data Source Query.5", "Data Source Query.6", "Data Source Query.7", "Data Source Query.10", "Data Source Query.11", "Data Source Query.12"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Data Source Query.9", "Content Length"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","Content-Length: ","",Replacer.ReplaceText,{"Content Length"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Content Length", Int64.Type}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Data Source Query.8", "Response Status"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns2", "Data Source Query.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Data Source Query.2.1", "Data Source Query.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Data Source Query.2.1", type text}, {"Data Source Query.2.2", type text}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type2",{{"Data Source Query.2.1", "HTTP Method"}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Renamed Columns3", "Data Source Query.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Data Source Query.2.2.1", "Data Source Query.2.2.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Data Source Query.2.2.1", type text}, {"Data Source Query.2.2.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"Data Source Query.2.2.2"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns1",{{"Data Source Query.2.2.1", "URL"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns4", "Relative Start Time", each [Start Time] - List.Min(#"Renamed Columns4"[Start Time])),
#"Calculated Total Seconds" = Table.TransformColumns(#"Added Custom",{{"Relative Start Time", Duration.TotalSeconds, type number}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Calculated Total Seconds", "Resource", "Resource - Copy"),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Duplicated Column", "Resource - Copy", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Resource - Copy.1", "Resource - Copy.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Resource - Copy.1", type text}, {"Resource - Copy.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type4",{"Resource - Copy.1"}),
#"Renamed Columns5" = Table.RenameColumns(#"Removed Columns2",{{"Resource - Copy.2", "File Or Folder Accessed"}}),
#"Inserted Total Seconds" = Table.AddColumn(#"Renamed Columns5", "Total Seconds", each Duration.TotalSeconds([Exclusive Duration]), type number),
#"Renamed Columns6" = Table.RenameColumns(#"Inserted Total Seconds",{{"Total Seconds", "Duration Seconds"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns6", each ([HTTP Method] = "#(lf)GET") and ([Content Length] <> -1))
in
#"Filtered Rows1"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment