Hello everyone,
I am on a fun sidequest to automate my workflow. Since an API is (currently) of the table i have resorted to be creative with PowerQuery. This is what i am attempting:
1) use powerquery to acces documents on an online platform (SharePoint)
2) Filter for relevant files, retrieve binary content and some meta data
3) Convert binary data to base64 and send the "content" to python for processing
4) Execute Python: determine a unique code based on the files content, and return.
(currently this is my main result, but i plan do add functionallity such as reconstructing PDFs)
5) Combine the results for later processing:
The workflow works, but for a number of files an error is returned:
An error occurred in the ‘’ query. DataSource.Error: ADO.NET: A problem occurred while processing your Python script.
Here are the technical details: Exception of type 'Microsoft.Mashup.Engine.Interface.ResourceAccessAuthorizationException' was thrown.
Details:
DataSourceKind=Python
DataSourcePath=Python
Message=A problem occurred while processing your Python script.
Here are the technical details: Exception of type 'Microsoft.Mashup.Engine.Interface.ResourceAccessAuthorizationException' was thrown.
ErrorCode=-2147467259
ExceptionType=Microsoft.PowerBI.Scripting.Python.Exceptions.PythonUnexpectedException
I tried debugging with help from AI, but so far i have been unable to uncover why these errors occure for specific files, but not all.
So far i made sure to:
- set the permisions to public
- buffer the binary content before passing it to python
- proces the binary content row by row
What causes the error for some files? How could i resolve the issue?
Many thanks for any help.
Additional remarks:
- I tested my python code by syncing Sharepoint locally. I needed to enable long file paths to acces files my current implementation returns errors for.
Technical details:
m-code (main):
let
Source = SharePoint.Files("url"),
#"Removed Columns" = Table.RemoveColumns(Source,{"Date accessed", "Date created", "Attributes"}),
Add_Locatie = Table.AddColumn(#"Removed Columns", "Bestandslocatie", each Text.AfterDelimiter([Folder Path],"requirement")),
Filter_Locatie = Table.SelectRows(Add_Locatie, each [Bestandslocatie]<>""),
Run_Python = Table.AddColumn(Filter_Locatie,"Hash", each fxHashFromBinary([Content])),
#"Removed Columns1" = Table.RemoveColumns(Run_Python,{"Content"})
in
#"Removed Columns1"
m-code: supporting function:
(FileContent as binary) =>
let
Base64String = Binary.ToText(Binary.Buffer(FileContent), BinaryEncoding.Base64),
InputTable = #table({"Base64Content"},{{Base64String}}),
HashedContent = Python.Execute("my code",[dataset=InputTable]),
Hashcode = HashedContent[Value]{1}[Hash]{0}
in
Hashcode
python:
import base64
import pandas as pd
import hashlib
from io import BytesIO
def Calculate_hash(base64_str, chunk_size=4096):
try:
raw_bytes = base64.b64decode(base64_str) # Convert base64 encoding back to raw bytes
file_stream = BytesIO(raw_bytes) # In order to hash the file in chunks, convert raw bytes into a stream
file_hash = hashlib.sha256() # create hash object to store hash code
while True:
chunk = file_stream.read(chunk_size)
if not chunk:
break
file_hash.update(chunk) # hash raw bytes
return file_hash.hexdigest()
except Exception as e:
return f'HASH_ERROR: {str(e)}'
Hashcode = Calculate_hash(base64_str=dataset['Base64Content'][0])
output_table = pd.DataFrame({'Hash': [Hashcode]})
