I'm using an API requiring a token in Base64 encoding, coming from a text string.
Playing around with the query editor and M, I've been able to succesfully convert the encoded string back to the original with Binary.FromText function and the BinaryEcoding.Base64 constant. But is there a way to do the opposite, so encoding the original string into Base64 format?
Solved! Go to Solution.
Try the formula below.
Try the formula below.
Hi @Michiel ,
Currentely sitting with the same issue as you did, gonna have to do a encode-decode to retrieve what will finally be my APi-token. Would you mind showing me your code on this?
Hi @Jonas1 ,
The demo M code below will give you an idea how it works:
let BaseToken = "<token><version>1</version><data>7B4A8AEB674D862950EC85F3ACD631777A6E63CBAC5A0A4FDE85DBFE0BF92AD6</data></token>", Decoded = Binary.ToText(Text.ToBinary(BaseToken, BinaryEncoding.Base64)), BaseEncodedToken = "PHRva2VuPjx2ZXJzaW9uPjE8L3ZlcnNpb24+PGRhdGE+N0I0QThBRUI2NzREODYyOTUwRUM4NUYzQUNENjMxNzc3QTZFNjNDQkFDNUEwQTRGREU4NURCRkUwQkY5MkFENjwvZGF0YT48L3Rva2VuPg==", DecodedBinary = Binary.FromText(BaseEncodedToken, BinaryEncoding.Base64), DecodedText = Text.FromBinary(DecodedBinary,TextEncoding.Windows) in DecodedText
The Decoded step shows the Base64 text version of the BaseToken string. The DecodedText step shows the decoded verson of the BaseEncodedToken string. The confusing thing here is that both are strings and you're basically encoding one into the other; but obviously you cannot include things like </version> in a HTML call directly.
Thanks @Michiel. Think maybe I have to give up using PBI for this though as my API also required additional stuff for the authorization process (hashlib and hmac if you are familiar with Python). Because your API only required base64 right?
I'm not familiar with that, but I must say that the web.contents function in M is pretty powerful. In my case, I ended up not using encoding at all but constructing the encoded string directly (long story to explain, but it allowed me to have separate Power Queries to list the fields I want to retrieve from a specific entity).
You do know that it's possible to call a Python script from M? There are some restrictions, though.
Oh wow, that is nice.
The only thread I could find on these other librairies said that it would either require some advanced coding, or I would have to subscribe for a program.
So I ended up using Python yes. Power BI Service does not support any of the required libraries, but I call the API in Python, transform the data there and import the output as text files to Power BI via the folder connector. Not the ideal solution considering I need to schedule upodates in Python as well now, but it works.
If you @Michiel or anyone else are looking for more opportunities with connecting to APIs, please give my suggestion a vote here. Python truly is an awesome and easy to use tool, that would great with a further integration in Power BI 🙂
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.