Skip to content

Instantly share code, notes, and snippets.

Avatar

Christopher Webb cwebbbi

View GitHub Profile
@cwebbbi
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
{
"format":"PDF",
"powerBIReportConfiguration":{
"settings":{
"locale":"en-US",
"excludeHiddenPages":"true"
},
"defaultBookmark":"H4sIAAAAAAAAA+1cW2/bOhL+K4EXi74Ei+FVZJ+2Tdo9xSanu0k…",
"pages":[
{
@cwebbbi
cwebbbi / PowerQueryADLSgen2GETRequests.m
Created Mar 3, 2021
Power Query function to extract all GET requests to ADLSgen2 storage
View PowerQueryADLSgen2GETRequests.m
(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 So
@cwebbbi
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
let
//Connect to the BBC News Top Stories RSS feed
//and create a nicely formatted table
Source = Xml.Tables(Web.Contents("http://feeds.bbci.co.uk/news/rss.xml")),
#"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
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)=>
let
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 =>
let
FilterRows = Table.SelectRows(ReplaceNullsInPath, each [Id]=pId and [Path]=pPath),
FirstRow = FilterRows{0},
Op = try FirstRow[Operation] otherwise "Trace Gaps"
@cwebbbi
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 =>
let
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"),