Loading M Queries from the Web into Power BI

Reuse your code by pulling queries from web servers

me@jaykilleen.com wrote this over 4 years ago and it was last updated over 4 years ago.


← Back to the Posts

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]

Similar