Skip to content

Instantly share code, notes, and snippets.


Christopher Webb cwebbbi

View GitHub Profile
cwebbbi / PowerQueryADLSgen2GETRequests.m
Created March 3, 2021 10:09
Power Query function to extract all GET requests to ADLSgen2 storage
View PowerQueryADLSgen2GETRequests.m
(SourceQuery as table) =>
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 So
cwebbbi / ExportToFileInGroupExamplePayload
Created April 25, 2020 19:45
Example payload for the Export To File In Group API endpoint in the Power BI REST API for exporting a paginated report
View ExportToFileInGroupExamplePayload
cwebbbi / PowerBIIncrementalRefreshBBCNews.m
Created April 12, 2020 19:48
A Power Query M query for use with Power BI incremental refresh that gets a list of top stories from the BBC News RSS feed
View PowerBIIncrementalRefreshBBCNews.m
//Connect to the BBC News Top Stories RSS feed
//and create a nicely formatted table
Source = Xml.Tables(Web.Contents("")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:version", Int64.Type}}),
channel = #"Changed Type"{0}[channel],
#"Changed Type1" = Table.TransformColumnTypes(channel,{{"title", type text}, {"description", type text}, {"link", type text}, {"generator", type text}, {"lastBuildDate", type datetime}, {"copyright", type text}, {"language", type text}, {"ttl", Int64.Type}}),
item = #"Changed Type1"{0}[item],
#"Changed Type2" = Table.TransformColumnTypes(item,{{"title", type text}, {"description", type text}, {"link", type text}, {"pubDate", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"guid"}),
cwebbbi / PowerQueryDiagnosticsSummary.m
Last active February 9, 2020 19:03
A Power Query M function that summarises Power Query diagnostics data, making it easier to interpret
View PowerQueryDiagnosticsSummary.m
(Input as table) as table =>
Source = Input,
#"Expanded Additional Info" = Table.ExpandRecordColumn(Source, "Additional Info", {"Message"}, {"Message"}),
#"Calculated Total Seconds" = Table.TransformColumns(#"Expanded Additional Info",{{"Exclusive Duration", Duration.TotalSeconds, type number}}),
#"Sorted Rows" = Table.Sort(#"Calculated Total Seconds",{{"Id", Order.Ascending},{"Start Time", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Id", "Query", "Category", "Operation", "Start Time", "End Time", "Exclusive Duration (%)", "Exclusive Duration", "Data Source Query", "Message", "Row Count", "Content Length", "Path", "Group Id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Message", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"Missing",Replacer.ReplaceValue,{"Path"}),
BufferedTable = Table.Buffer(#"Replaced Value"),
cwebbbi / DiagnosticsForDecompTree.m
Created February 2, 2020 21:17
An M function to format Power Query diagnostics data in a way that's suitable for the Power BI Decomposition Tree visual
View DiagnosticsForDecompTree.m
(Input as table)=>
Source = Table.Buffer(Input),
RemoveColumns = Table.SelectColumns(Source,{"Id", "Query", "Step", "Category", "Operation", "Exclusive Duration", "Path"}),
ReplaceNullsInPath = Table.ReplaceValue(RemoveColumns,null,"",Replacer.ReplaceValue,{"Path"}),
GetNodeOperation = (pId, pPath) as text =>
FilterRows = Table.SelectRows(ReplaceNullsInPath, each [Id]=pId and [Path]=pPath),
FirstRow = FilterRows{0},
Op = try FirstRow[Operation] otherwise "Trace Gaps"