Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Michiel
Resolver III
Resolver III

Encode a Text field

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?

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi @Michiel,

 

Try the formula below.

Binary.ToText(Text.ToBinary([Column1]), 0)

base64

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @Michiel,

 

Try the formula below.

Binary.ToText(Text.ToBinary([Column1]), 0)

base64

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Is it so simple... Thanks!

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 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.