Skip to content

Instantly share code, notes, and snippets.


Christopher Webb cwebbbi

View GitHub Profile
cwebbbi / DiagnosticsForDecompTree.m
Created Feb 2, 2020
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"
cwebbbi / PowerQueryDiagnosticsSummary.m
Last active Feb 9, 2020
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 / PowerBIIncrementalRefreshBBCNews.m
Created Apr 12, 2020
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 / ExportToFileInGroupExamplePayload
Created Apr 25, 2020
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 / PowerQueryADLSgen2GETRequests.m
Created Mar 3, 2021
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