Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have an existing PowerBI report which utilizes DirectQuery to read from a PostgreSQL database. I recently added a new Postgres table that has a column of base64 encoded text. I need to decode it from Base64 and display it on my PowerBI report. I tried to add a new column and use the function Binary.FromText to decode it, as mentioned in this post:Is there a way to decode a base64 text field?. It didn't work for me because I am using DirectQuery.
let
Source = PostgreSQL.Database("xx-xxxx-xxxx-xx.xxx.xxxx.xxx:xxxx", "xxxxxx"),
dcsd_s_postbibfeedbackoutput = Source{[Schema="dcsd_s",Item="postbibfeedbackoutput"]}[Data],
#"Added Custom" = Table.AddColumn(dcsd_s_postbibfeedbackoutput, "decodedRecord", each Lines.FromBinary(Binary.FromText([originalRecord],BinaryEncoding.Base64), null, null, 1252){0})
in
#"Added Custom"
I don't think I can switch to Import mode (instead of DirectQuery) because of two reasons:
1) the report needs to be updated ASAP when new data is available.
2) the amount of data that I'm loading to this report is huge.
I also thought about decoding the string in Postgres first, and then reading it from PowerBI. But I was told that the string might get distorted when PowerBI reads it from Postgres.
Is there any way for me to decode these base64 text strings in PowerBI while using DirectQuery?
Thank you for your time.
Solved! Go to Solution.
direct query mode relies on querying the data source in realtime, and transformations like Base64 decoding happen within Power bi's internal engine, you can preprocess the data in etl to to decode then import it in powerbi. or you can go for source level decoding ie decode at database level
I think @AnalyticPulse is right. Decoding isn't likely compatible with DirectQuery since it breaks query folding. The best option would be to decode it in the database before connecting.
Alternatively, it's likely possible to decode using DAX but you'd have to write your own custom logic to do that.
Hi @pelinoa
Thanks for the solution @AlexisOlson and @AnalyticPulse provided, and i want to offer some more information for you to refer to.
Based on the picture you have offered, do you want use the base 64 to display picture? If you want to implement, you can change the base 64 to image url in data category in Power BI Desktop, you can refer to the following picture.
And you can refer to the following article about more.
Embedding Images in Power BI using Base64 – Some Random Thoughts (sqljason.com)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
No, this is not a picture. It is a marc record that was encoded to base-64, and should be decoded for the user to properly view it. Thank you.
I think @AnalyticPulse is right. Decoding isn't likely compatible with DirectQuery since it breaks query folding. The best option would be to decode it in the database before connecting.
Alternatively, it's likely possible to decode using DAX but you'd have to write your own custom logic to do that.
direct query mode relies on querying the data source in realtime, and transformations like Base64 decoding happen within Power bi's internal engine, you can preprocess the data in etl to to decode then import it in powerbi. or you can go for source level decoding ie decode at database level
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.