Is there a way to decode a base64 text field?

[Anonymous]

I was trying to find a way to decode a text field that has been encoded in base64?

I know there is a function named BinaryEncoding.Base64 which I assume allows you to encode a string, but I can't see a decode one in the docs?

Many thanks,

Dom

[luizhrocha]

You can use Text.FromBinary - PowerQuery M | Microsoft Docs.

[v-shex-msft]

Hi @Anonymous ,

Actually, BinaryEncoding.Base64 only means the encoding format.

According to your description, I think binary functions will suitable for your requirements. You can use power query functions to encode and decode your characters.

Reference links:

Binary.ToText	Encodes binary data into text format.
Binary.FromText	Decodes data from text into binary.

Sample:

Custom = Binary.ToText(Binary.FromText("0200057b094a3a00e57d000000000000", BinaryEncoding.Base64),BinaryEncoding.Hex)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

Thanks or your response, that got me a step further, however I need to the string to be converted to a string value rather than a hex value. 

e.g. the value

ZWRlMzc4NTUtMjA5ZS00NDNmLWFhYWItZmI4MTU0ZTVmOTVj

would be converted to the string value:

ede37855-209e-443f-aaab-fb8154e5f95c

instead of the hex value:

65646533373835352d323039652d343433662d616161622d666238313534653566393563

Is that possible? 

Many thanks,

Dom

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

I think below formula will suitable for your requirement:

Lines.FromBinary(Binary.FromText(text,BinaryEncoding.Base64), null, null, 1252){0}

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WigoPyvGtSjbxCwkt8c1yNI0KNjDwc/HL9Ql3y4gM9yyJyvU08Q0JNYgKCcv1DwnLUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Lines.FromBinary(Binary.FromText([Column1],BinaryEncoding.Base64), null, null, 1252){0})
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

[a_o_letsgo123]

Hi Xiaoxin,

I've placed this code but the table seems to be empty:

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t])

Is there a reason why? 

[lbendlin]

Because "i44FAA=="  decodes and decompresses into "[]" which is an empty array.

[Anonymous]

Thanks Xiaoxin that worked a treat!