Reuse your code by pulling queries from web servers
me@jaykilleen.com wrote this almost 8 years ago and it was last updated almost 8 years ago.
Today I am experimenting with loading an M Query from a page on a website.
Ideally this could be done with a Github Gist but for some reason Github gets blocked on our network so I'll try by pulling from an HTML table on this blog.
I see an opportunity here where Queries can be packaged like JavaScript is and loaded directly to the Query Editor.
Experiment 1: Create an HTML table with a simple 'Hello World' M Query inside a row. The current 'Get Data' from web only seems to support HTML from the DOM.
| Query |
|---|
| let Source = "Hello World" in Source |
So we can pull this into our PowerBI Query Editor to return "Hello World" using:
let
Source = Web.Page(Web.Contents("http://jaykilleen.com/posts/loading-m-queries-from-the-web-into-power-bi")),
Data0 = Source{0}[Data],
ChangeType = Table.TransformColumnTypes(Data0,{{"Query", type text}}),
DrillDownToQuery = ChangeType{0}[Query],
FinalQuery = Expression.Evaluate(DrillDownToQuery)
in
FinalQuery
This feels pretty clumsy but it works.
Now I'd like to try this with a query that is pulling a table from SQL Server. The rubbish thing with the way I am doing this is I have to minify the query to fit it into my Github Flavoured Markdown table. I'll have to try and find a better way of doing this.
| QueryName | Query |
|---|---|
| Date | let Source = USGBoralSalesDW, dbo_dimDate = Source{[Schema="dbo",Item="dimDate"]}[Data], MergedFinancialPeriod = Table.NestedJoin(dbo_dimDate,{"PeriodKey"},FinancialPeriod,{"ID"},"FinancialPeriod",JoinKind.Inner),SelectColumns = Table.SelectColumns(MergedFinancialPeriod,{"DateKey", "FinancialYearKey", "FinancialQuarterKey", "FinancialQuarterName", "PeriodKey"}) in SelectColumns |
| Account | let SFDC_Account = SFDC{[Name="Account"]}[Data], SelectColumns = Table.SelectColumns(SFDC_Account,{"Id", "IsDeleted", "Name", "RecordTypeId", "CurrencyIsoCode", "OwnerId", "CreatedDate", "Active__c", "Buying_Account__c", "Unique_ERP_Account_Number__c"}), FilterActive = Table.SelectRows(SelectColumns, each ([Active__c] = true) ), MergeCurrency = Table.NestedJoin( FilterActive,{"CurrencyIsoCode"}, Currency,{"IsoCode"}, "NewColumn", JoinKind.Inner), FilterIsDeleted = Table.SelectRows(MergeCurrency, each ([IsDeleted] = false) ), JoinExpandAccountRecordType = Table.ExpandTableColumn( Table.NestedJoin( FilterIsDeleted,{"RecordTypeId"}, AccountRecordType,{"Id"}, "RecordType", JoinKind.LeftOuter ), "RecordType", {"Name"}, {"RecordType.Name"} ) in JoinExpandAccountRecordType |
| HelloWorld | let Source = "Hello World" in Source |
With this table we can now build a function
let
Source = (LoadQuerySource as text, LoadQuerySourceTableName as text) => let
// Source
WebPage = LoadQuerySource,
Source = Web.Page(Web.Contents(WebPage)),
// Transform
Data = Source{1}[Data],
ChangeType = Table.TransformColumnTypes(Data,{{"QueryName", type text}, {"Query", type text}}),
// Evaluate
FindQuery = Table.SelectRows(ChangeType, each [QueryName] = LoadQuerySourceTableName),
FoundQuery = FindQuery{0}[Query],
EvaluateQuery = Expression.Evaluate(FoundQuery, #shared)
in
EvaluateQuery
in
Source
And invoke this function using:
let
Source = GetQueryFromWeb("http://jaykilleen.com/posts/loading-m-queries-from-the-web-into-power-bi", "Date")
in
Source
To pull in the final table as evaluate in the query.
Neato!
To make this work you will need to create two parameters:
\\ LoadQuerySource
null meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
and
\\ LoadQuarySourceTableName
null meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=false]