Forum Discussion

AnnieV's avatar
AnnieV
Frequent Visitor
1 year ago

scheduled refresh and dynamic queries

I have a need to import thumbnail photos into a Power BI report. 

I have trimmed a .pbix down to the bare minimum to demonstrate the issue. 

Directly entered data table "People" that has a Name and an ID

Thumbnails table hold an ID, and a file path to a thumbnail image, defined as: 
let
Source = Excel.Workbook(File.Contents("C:\PhotoImport\PhotoList.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Image", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "content", each Web.Contents([Image])),
#"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "parser", each localParse([content])),
#"Expanded parser" = Table.ExpandTableColumn(#"Invoked Custom Function", "parser", {"imgOrder", "base64"}, {"parser.imgOrder", "parser.base64"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded parser",{{"parser.imgOrder", Int64.Type}, {"parser.base64", type text}})
in
#"Changed Type1"

---

The localParse function is defined as:  

let
Source = (BinaryContent as binary) => let
SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000),
BinaryText = Binary.ToText(BinaryContent, BinaryEncoding.Base64),
SplitText = SplitTextFunction(BinaryText),
AddIndex = List.Generate(() => List.Count(SplitText) - 1, each _ >= 0, each _ -1, each { _, SplitText{_}}),
FinalTable = #table(type table [imgOrder = number, base64 = text], AddIndex)
in
FinalTable
in
Source

--- 

And finally a measure is added to the Thumbnails table as: 

showPhoto = IF(HASONEVALUE(Thumbnails[Image]),"data:image/jpeg;base64, " & CONCATENATEX(Thumbnails, Thumbnails[parser.base64], , Thumbnails[parser.imgOrder], ASC))

The showPhoto measure has a data category of Image URL

Displays beautifully, all is happy. UNTIL....

We need to set up a scheduled refresh for the report that is in import mode. 

Power BI service objects,
 


I understand this to be because of the concatenation going on with the base64 strings. 

I have tried all sorts of potential solutions, including: 

1. Turning off Include in Report Refresh for the Thumbnails query (it would only add new photos once a year)
2. Creating another table that pre-concatenates the base64 strings.
3. Turn off include in refresh for the table in #2. 
4. Export the results of the full concatenation into a csv file of it's own, so I can import a static version of it. 
5. Abandon the parse and load the photos as binary objects into a Fabric data table (Power BI doesn't seem to handle making that binary available in the data pane). 
6. Added a step to parse the binaries from the Fabric data table into base64 again. 

All of these options result in a broken image icon rather than actual image rendering, and at all times has the measure or full base64 data been categorized as Image URL. 

What I cannot do is put them out on an IIS server where they are publicly accessible, where I could just go pull the data category as Web URL. This involves an educational institution with FERPA and other privacy concerns. Any other storage options on the network require some form of authentication: files shares, SharePoint file storage, etc, and don't work for that purpose. 

I've read a few threads here that are dealing with this kind of topic, including one that suggested dealing with data privacy firewalls 
which I'll admit I don't fully grasp. 

What else could I possibly do to be able to display these images, and also have a scheduled refresh on the other data? 

Files available at Google Drive share with silly sample images







6 Replies

  • Hi. It looks like you are scrapping the pictures from the web. If you have the picture public urls, you don't need to do a web.contents url. You can just load the table with the URL and categorize that column at the data view as "Image URL" to make it display at some visuals.

    If you want to scrap them, then the trick is that each picture has a URL, so how can PowerBi think about a single source if there is one by line. You can read this post to cheat on power bi letting it think the url is only one but the relative path is the different one: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

    I hope that helps, 

    • AnnieV's avatar
      AnnieV
      Frequent Visitor

      Actually I'm not scraping from the web, that's exactly what I can't do. The localParse functions reads from a local drive, and encodes the base64 strings, but then we run into the issues described when trying to set up a scheduled refresh, and the various things I've tried to get around it. 

      • ibarrau's avatar
        ibarrau
        Icon for Super User rankSuper User

        I know localParse is trying to handle base 64, but this line "each Web.Contents([Image]))" is running a web request to a string. What is Imagen? isn't that a url? that's the one I was talking about to pay attention when I was talking about scrapping.

        I hope that make sense