This post shows how to call an Azure Function from Power BI. This scenario offers some interesting possibilities, because it allows for integrating the power of several common programming languages with Power BI and Power Query. If you’re not familiar with Azure Functions, it’s a service that allows you to run small pieces of code without having to deal with a server. In this example, we’ll write a query in Power BI that submits a time zone to an Azure Function. The function simply returns the current time in the specified time zone. While this is a very basic example, it is important because itRead More →
Update 2016/06/01: Bill Szysz has shared a solution in the comments below that is shorter and easier to understand than mine. He’s also shared a couple of other alternatives that seek to improve the randomness of the results. This post describes how to generate a random alphanumeric string in Power Query. This is likely not a common requirement for most Power Query users, but I saw this requirement in the Twitter API and thought it would be a fun challenge in M. Here’s the complete query. Later I’ll explain how it works.
Generates a single, random alphanumeric string. The string length can be modified by changing the StringLength variable.
Author - Chris Koester
StringLength = 32,
ValidCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456879",
fnRandomCharacter = (text) => Text.Range(ValidCharacters,Int32.From(Number.RandomBetween(0, Text.Length(ValidCharacters)-1)),1),
GenerateList = List.Generate(()=> [Counter=0, Character=fnRandomCharacter(ValidCharacters)],
each [Counter] < StringLength,
each [Counter=[Counter]+1, Character=fnRandomCharacter(ValidCharacters)],
RandomString = List.Accumulate(GenerateList, "", (a,b) => a & b)
The result of this query is a value like “kZSRd55cC67QyFWbjTXKlhnwiCttaZxU”. Every time the query is run,Read More →
Update 2016-04-27: Imke Feldmann sent me an M query that accomplishes the same goal of this post with less than half the code! The query is here. Imke’s blog is a great resource and you can also find her on Twitter. Thank you, Imke! Basic JSON structures can be parsed pretty easily by Power Query, as they often represent tabular structures that are familiar to those that work with data. Power Query simply converts the JSON to a table and you’re good to go. Working with a JSON array in Power Query, however, can be difficult and may result in duplicate rows in your dataset. JSON is builtRead More →
Use Microsoft’s Power Query tool to retrieve data from the Twitter APIRead More →