D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Display D365 Image in Power BI

  • 1.  Display D365 Image in Power BI

    SILVER CONTRIBUTOR
    Posted Jun 05, 2019 12:40 PM
    Hi,

    Has anyone ever try to display image in Power BI from D365 table ? Take example : EcoResProductImage table

    Here is what I did, only it is not working as expected :

    1. Open Power BI desktop
    2. Get Data -> SQL Server and choose AXDB. (Understand this is not available when we 're at Production environment)
    3. Choose table : EcoResProductImage.
    4. Choose Direct Query for storage mode.
    5. Don't choose Load, but Edit
    6. In Edit Query, go to Advance Query, change the existing M language with this :

        let
        Source = Sql.Database(".", "AxDB",
        [Query="select cast('' as xml).value('xs:base64Binary(sql:column(""thumbnailsize""))', 'varchar(max)') as [Image] from ecoresproductimage", CreateNavigationProperties=true])
    in
        Source


    7. This is to directly change / convert to Binary field in EcoResProductImage.Thumbnailsize to string with Base64 encoder. I happened to check also the code in X++ on how they use the image field and want to show it for example to SSRS report. With a little bit understanding, actually not very sure whether it is correct to say that point no 6 is the same method.

    8. Close and apply.
    9. At report view, go to Modelling Tab, Add new column
    10. Type this : Photo = "data:image/png;base64," & EcoResProductImage[Image]
    11. Press Enter, and still at the new column, select Data Category and change to Image URL
    12. Drag the new column (photo) to report, with Table visualization

    13. Some addition, maybe need to check whether the length is < 32766 char. For me, I change the syntax of the new column to this :
           Photo = IF(LEN(EcoResProductImage[Image]) < 32000, "data:image/png;base64," & EcoResProductImage[Image], BLANK())

    14. Reason is, it's just cannot more than 32766 char for the string size.

    I'm wondering whether there is someone out here succeeded to display it ?
    Since as I mentioned in point 7, even D365 code is using the same method, that is change the Binary column of the image field to string with base64 encoder. Unless of course I read the code wrongly.

    And with the same steps, actually it is succeed but with sample database AdventureWorksDW. I'm using tale dimProduct and it is succeeded on display the dimProduct image in table visualization of Power BI.

    There couples of blog suggesting it will work, only they are not using D365 as the source and also using Import method for Power BI storage.
    Here are the references :

    http://sqljason.com/2018/01/embedding-images-in-power-bi-using-base64.html
    https://blog.gbrueckl.at/2018/01/storing-images-powerbi-analysis-services-data-models/

    Any advise is highly appreciated.

    Thanks.




    ------------------------------
    An Ton
    Hunter Douglas
    ------------------------------


  • 2.  RE: Display D365 Image in Power BI

    SILVER CONTRIBUTOR
    Posted Jun 17, 2019 11:21 AM
    Hi,

    Any advice on this ?
    Thanks,

    ------------------------------
    An Ton
    Hunter Douglas
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role