The Power BI Service is something that many people struggle with in terms of coping with Power BI. Many people start with Power BI Desktop – the report authoring environment – before then starting to publish content to the Power BI Service, often that is where it ends initially, the Service has so much more to it than just that. In this session we look at what we need to do to make our Food Hygiene dataset work in the Power BI Service so the refresh can be automated.
The Power BI Service needs to have defined connections with datasets to set up a Schedule Refresh. The function we wrote and adapted on Weeks one and two does not do that because the URL is a parameter. This is a situation you can get into with parametrisation for different environments. So this is something to be aware of and prepared to solve.
In our example the solution is to move to using a Relative Path, this is a M Script option that effectively appends a static URL with an extension, in our case the file to be downloaded.
Xml.Tables(Web.Contents("http://ratings.food.gov.uk", [RelativePath= "OpenDataFiles/"& FileName])) // FileName is a Parameter [RelativePath=<<specific element>>] Note how M Script is simple in construction, but looks complex in essence though recognise that the line is nested function, XML.Tables( ) and Web.Contents( ).
XML.Tables ( ) is expecting a stream of text to be processed into a Table of Data using the embedded schema (remember XML carries it’s schema with it).
Web.Contents ( ) is used to get the data from the Internet (rather than a local or networked computer), we are providing it a URL, this will ensure there is a stable connection in the Power BI connections, with the “OpenDataFiles/” & FileName being used to provide the specific file details. The use of RelativePath removes the “Some data sources may not be listed because of hand-authored queries” error in the data source settings.
