Can Power Query populate binary data into data model?

[EdwardH]

Simple query, configured with Only Create Connection and Add this data to the Data Model, works without issue, and is accessible to Excel Python xl() function.

let Source = "Hello World!" in Source

However, it breaks if it's considered binary data:

let Source = Text.ToBinary("Hello World!") in Source

 Now we get an error "Load to worksheet  failed" and the following for detail:

It doesn't make sense to me, because I'm not asking to load it into the worksheet or display anything, I just want the raw data in the data model so that I can access it using Python/xl().

One workaround I figured out is to base64 encode it, and then I can base64 decode it on the other end.

let Source = Binary.ToText(Text.ToBinary("Hello World!"),BinaryEncoding.Base64) in Source

But that uses more memory and just feels silly.

Is there a way to get Power Query to load a binary value into the data model other than the workaround I found?

My use case is that I have an LZMA-compressed file that I want to decompress in my workbook. Python can't access the file directly, I have to use Power Query to retrieve the file. I would prefer to have Power Query just load the raw binary data into the data model to make it accessible to the Python code via xl().

[Omid_Motamedise]

I did the same as you and it work for me in the only connection senario

you can also apply the python code in the middle of power query steps useing APIs.

[EdwardH]

Hi Omid, I've been searching and I haven't been able to find how to call python code in the middle of Excel Power Query steps. I think I see how to do it in PowerBI. Could you point me to where I can learn how to do it in Excel Power Query?

[EdwardH]

Omid, thanks for testing. Did you have "Add this data to the Data Model" checked?

I'll have to look into creating python functions that you can call inside of Power Query. That would be awesome!

[Omid_Motamedise]

No I didn't load data in data model

For running Python in the middle of excel query, you can do it directly, but you can use the websites like 

https://www.pythonanywhere.com/

To send the python code and receive its result

[v-sgandrathi]

Hi @EdwardH,

Thank you for being a part of the Microsoft Fabric Community.

I wanted to check if you had the opportunity to review the information provided by @Omid_Motamedise, Please feel free to contact us if you have any further questions.

If my response has addressed your query, please Accept it as a solution and give a Kudos so other members can easily find it.

Thank you.

[EdwardH]

Hello,

I reviewed Omid's response. The response did not address my query.

[v-sgandrathi]

Hi @EdwardH,

Providing the workaround which might assist you in addressing the issue.

Splitting Binary Data into Chunks

If memory usage is a concern, you can divide the binary data into smaller chunks, such as rows of text.

Use Binary.ToList() to convert the binary data into a list of integers, and then transform this list into a table in Power Query. For example:

let

   Source = Text.ToBinary("Hello World!"),

   BinaryList = Binary.ToList(Source),

   Table = Table.FromList(BinaryList, Splitter.SplitByNothing(), {"Byte"})

in

   Table

You can reassemble the data in Python by converting the list of integers back into binary.

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

Your understanding and patience will be appreciated. Let us know if further assistance is needed.

Thankyou.

[v-sgandrathi]

Hi @EdwardH,

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution.

It would be greatly appreciated by others in the community who may have the same question.

Thanks for connecting with the Microsoft Fabric Community Forum.

[v-sgandrathi]

Hi @EdwardH,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thanks for reaching out to the Microsoft Fabric Community forum.

[v-sgandrathi]

Hi @EdwardH,

Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help. 

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it.

Thanks for reaching out to the Microsoft Fabric Community forum.