Forum Discussion

AltusTellus's avatar
AltusTellus
Helper III
4 years ago

Convert Binary data

Hi experts,

 

I have an issue with converting binary data to a readable PDF file. What I have done until now:

 

In a MS SQL table with financial transaction lines, I have a column with the binary data of the document which is attached to the row with the financial transaction. The lenght of the binary data is about 34.000 positions.

 

In PBI Desktop the data has a maximum of 1024 positions in the column [Document]. I have added a new column with the formula:

 

Binary.ToText([Document], BinaryEncoding.Base64)

 

In this new column (named: [Document_Def]) I only have errors. By clicking next to [Error] I see the details:

 

Exression.Error: Cannot convet the value "JVBERi0xLjQKJeLjz9MN..." to Binary type. After that followed bij the complete text of this field and ends up with Type=[Type].

 

Is there a solution for this? At the end I want the data to be shown as a PDF-document by PDF Viewer.

 

Many thanks foor answers / suggestions!

 

Grtz, Alfred

 

 

Need Help

10 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi AltusTellus ,

     

    Please check if the type of [document] column is Binary. 

     

     

    Or you can try the following formula to see if it works.

     

    Binary.ToText(Binary.From([Document]), BinaryEncoding.Base64)

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • AltusTellus's avatar
      AltusTellus
      Helper III
      4 years ago

      After the import into PBI Desktop, indeed, the [Document] column is of the type binary. I also used your formula, unfortunately with the same issue. Here you can find the orginal data and the data in PBI after the import. 

      Document example SQL to PBI 

       

      You will see that the total lenght of the original document is approximately 34.000 characters. In PBI column it is 1.024 characters after your formula. 

       

      Looking forward to your reply, many thnx!

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        Hi AltusTellus ,

         

        I can't reproduce your problem, could you allow editing the shared excel? That way I can test it based on your data. Currently I only can read the excel and cannot copy the data.

         

        Best Regards,
        Winniz

  • Naïma's avatar
    Naïma
    New Member
    1 year ago

    Bonjour à tous, 

     

    Désolée je ne saurais pas expliquer en anglais 😉 Pour ma part, je rencontrais exactement le même problème car je passais par "Transformer le fichier" et du coup c'était du Binary. J'ai trouvé une manière de l'éviter en ne passant donc pas par "Transformer le fichier" mais plutôt en important le classeur Excel. Voici le code M que j'ai utilisé pour contourner l'erreur : 

    let
    Source = SharePoint.Files("xxxxx", [ApiVersion = 15]),
    #"Lignes filtrées" = Table.SelectRows(Source, let latest = List.Max(Source[Date modified]) in each [Date modified] = latest),
    #"BDD_Reseau_20240108_PARTAGE xlsm_https://xxxxcom/sites/xxxxx/Documents partages/General/" = #"Lignes filtrées"{[Name="BDD_Reseau_20240108_PARTAGE.xlsm",#"Folder Path"="https://transportsagglomerationde.sharepoint.com/sites/BaseArretTaM/Documents partages/General/"]}[Content],
    #"Classeur Excel importé" = Excel.Workbook(#"BDD_Reseau_20240108_PARTAGE xlsm_https://xxxx/sites/xxxxx/Documents partages/General/"),
    #"Lignes filtrées1" = Table.SelectRows(#"Classeur Excel importé", each ([Name] = "Offre")),
    Offre_Sheet = #"Lignes filtrées1"{[Item="Offre",Kind="Sheet"]}[Data]
in
    Offre_Sheet