cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Encode a Text field

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
Highlighted
Microsoft
Microsoft

Re: Encode a Text field

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

Highlighted
Resolver III
Resolver III

Re: Encode a Text field

Is it so simple... Thanks!

Highlighted
Helper I
Helper I

Re: Encode a Text field

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? 

Highlighted
Resolver III
Resolver III

Re: Encode a Text field

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.

 

Highlighted
Helper I
Helper I

Re: Encode a Text field

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? 

Highlighted
Resolver III
Resolver III

Re: Encode a Text field

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.

Highlighted
Helper I
Helper I

Re: Encode a Text field

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors