View images in Power BI

[Josh]

Hi @ivanradisson, that’s great to hear! You are correct, that is the relevant change for connecting to your data using PowerBI, etc.

There is currently an issue with authenticating from PowerBI with the redirect method used, so currently one must share the project data publicly for it to work with Power Query. This change will be in the next release to fix that issue.

[siwei]

Dear @Josh,
the new Synchronous Export feature is fantastic and our team would like to implement it quickly in order to retrieve media urls trough api calls in Power Platform. Do you have any information about the release date of the next fix? And until then, is there any other way to access the media url trough api? We tried with Authorization token but didn’t work.

Thanks a lot.

[Josh]

Hi @siwei, I’m glad you’re finding it useful there is no fixed date for the next release but we are wanting to get it out as soon as possible. Before then, you can still make use of the feature in PowerBI (and include the media URLs) by making the data public (since the issue is with PowerBI’s process of authenticating requests).

[janna]

Hi There!
Thanks for this thread - I’m facing similar challenges/needs to display the images I’ve collected in Power BI. I cannot make the data public, as the data stored is personal (which I assume many humanitarian and development users of KoBoToolbox might also be facing).

When making the ‘_attachments.download_url’ column (accessed through the API into Power BI) into a ‘Web URL’ in Power BI - and clicking it - it’s taking me to a KoBoToolbox page that says 500 Error.

This is quite strange, as about half an hour ago, I was at least able to enter my KoBo Credentials, and it pulled in thumbnail images from KoBoToolbox.

So, as you mentioned previously - I’m assuming this has something to do with Power BI’s authentication requests? But not sure why it was working a few minutes ago, and now isn’t working?

However, my real question is: I’m just trying to have a good solution for accessing images and displaying in a dashboard.

One idea I had was to somehow duplicate the images over into a web server (e.g., AWS) that could then have images set to public (with periodic updates) (which doesn’t require also having the private data set to public) - and then linking from Power BI to the path where the duplicated images are stored.

Just seeing if anyone has worked through this previously and would be available to walking through a good work-flow with me?

Thanks!
Janna

[ivanradisson]

@janna

One idea I had was to somehow duplicate the images over into a web server (e.g., AWS) that could then have images set to public (with periodic updates) (which doesn’t require also having the private data set to public) - and then linking from Power BI to the path where the duplicated images are stored.

This is an important question, one that many users out there have to face sooner or later.

The project I’m working with deals with public data overall, and so I have the form set to public, and it hasn’t been an issue so far. But really the only reason to have it public is because of the photos. If there was a workaround, it would be great and I would set the forms to private.

One idea is if Kobo Toolbox had a setting that allowed us to just share the photos publicly, but not the forms and answers.

[ivanradisson]

When the team releases the new fix, will that also apply to media files ?

To be more clear about my question:
Is it going to be possible to show photos in PowerBI (using the Kobo API connection) from a Kobo form that does not have those two sharing options enabled ?

[ivanradisson]

Hi @ivanradisson, you are correct in that the _uuid value is the culprit. Each time a submission is edited, a new uuid (or instanceID ) value is created and the old value becomes the deprecatedID . You can see this if you view the XML of your submissions.

Btw, is this behavior ever going to change? I mean, are there any planned updates?

I’m asking because this creates some problems for people like me, using Power BI.

If someone edits a form submission, then it gets a different UUID, but the media is still accessible by a URL that is based on the original UUID. As such, because I’m dynamically forming an URL that is based on whatever the current UUID is, then the images no longer show up in Power BI as you can see in the screenshot below.
This is a table visual and I show some data as well as photos on each column:

The arrow points towards the images that are not loading correctly (because that specific form submission has been edited).
The other parts I had to censor, but I hope it’s still clear.

Edit 1:
I guess my real question is:
With the update that was released in March 17th, is there anything I can use to overcome situations like the one I mentioned above? I connected to a new Kobo form just a couple of days ago and I still don’t see the full URL to the photos, anywhere in the data.
So as I said, my solution is to form the URL in a new column, in PowerBI.

This is how i generate the original image URL
https://kc.humanitarianresponse.info/media/original?media_file=pdul_admin/attachments/" & [#"formhub/uuid"] & "/" & [_uuid] & "/" & [#"group_photo1/input_photo1"]

This is how i generate the thumbnail URL
https://kc.humanitarianresponse.info/media/small?media_file=pdul_admin/attachments/" & [#"formhub/uuid"] & "/" & [_uuid] & "/" & [#"group_photo1/input_photo1"]

Note: group_photo1/input_photo1 is the name of the image field in Kobo.
With the exception of edited submissions, these URL work fine!

On those edited submissions, I have to manually replace values in the _UUID column. I replace the new ID by the old ID. And then my generated URLs work again. But obviously, this is not a good solution, only a temporary one.

Edit 2:
Just noticed this column called “Attachments” and it seems it has a list of all the URL in there (original, small, medium sizes).

Now to expand on this…

[caneeraj]

HI @Josh

are you referring to media ( Image ) link download via Power BI , if yes , can you please connect me to exact part of this update as i am not able to find this.

Cheers !

Neeraj

[Josh]

Hi @caneeraj, you can refer to the Synchronous Exports description in the release notes:

Release Notes - version 2.022.08, 2.022.08a

Hello, everyone. We’ve just launched a new release (effective at 21:00 UTC on 17 March 2022) on both kf.kobotoolbox.org and kobo.humanitarianresponse.info. Components comprising this release: KPI version 2.022.08 KoBoCAT version 2.022.08 Enketo Express version 2.8.1 Pyxform version 1.7.0 Security Fix This release includes a fix for an issue discovered by a recent security audit. If the following conditions were all met: A project allowed anonymous submissions; A person knew of the existen…

We will be releasing more comprehensive documentation on using the feature soon, but that should be sufficient to get you started.

[york_rff]

@ivanradisson

y replace values in the _UUID column. I replace the new ID by the old ID. And then my generated URLs work again. But obviously, this is not a good solution, only a temporary one.

Dear Josh, I understand that a new release was planned which will allow to download and show images in PowerBI without making all the data public. Is it foreseeable by now when this new release will be published?

Kind regards
York

[Josh]

Hi @york_rff, yes, this should be possible with the synchronous exports: Connecting to your data using synchronous exports — KoboToolbox documentation

[york_rff]

…not sure whether I understand: In the linked article, it seems to say very prominently, that the data MUST be made publicly available. Not so?

[Josh]

Hi @york_rff, you’re right… I should have shared this article instead about connecting to Power BI

https://support.kobotoolbox.org/pulling_data_into_powerbi.html

[york_rff]

Thanks a lot.

You mean this article is not just about an alternative way to “import” the kobotoolbox data into PowerBi (we currently do it with json), but this alternative way (other than with the json-method) allows to show images in PowerBi?

EDIT:
Looks like the answer is: no. So, is the only option to download the images to a public server and take it from there to at least keep the rest of the data private? Or is there a new release coming which adresses the issue?

[Josh]

Hi @york_rff, the article describes a method connecting your data on Kobo to Power BI, etc. in the same format that you can download the data asynchronously and manually import. These exports will include URLs to your submission media files. From there it is up to the client on how they are able to pull images from those URLs to display. If the client is unable to make authenticated requests do this, then you’ll have to make your data public or, as you suggest, move the images to another location that doesn’t require authentication.

[york_rff]

Dear Josh, thanks for the further input, though I am still not fully sure whether you are saying that without making the data public there is simply no way to dynamically (API etc.) load the image files themselves directly to PowerBI or indirectly via a private server (but that manual workarounds like “Downthemall” need to be applied) and that there are no plans to make this possible. Ist that the case?

I would already be fine, if the public/private choice was possible separately for image type data (so that images can be put on public while the rest of the data will remain private). Anything like that in the making?

[york_rff]

Dear all, any experiences / best practices around Janna’s idea to first transfer the image files to another server and then let PowerBI query them from there?

Instead of a server where I eventually have the same public/private status issue, I tend to believe that it needs to be a Microsoft cloud product (Sharepoint?), so that it might be able to become part of PowerBI’s authentification system?

Any input (including failed attempts) appreciated!

[york_rff]

Dear all who are interested in a simple way to make (a very big number of) photos from kobo questionnaires dynamically available in PowerBI. What is your simplest solution, you have found?

By the way: “Simple” doesn’t mean to lose the link to the form id (otherwise it is not possible to filter the photos / relate them to the right context).

EDIT (as not third “Reply” is allowed to this obviously neglected thread):

For who ever is struggling to get images smoothly into PowerBI, here is how we are trying it with some success at the moment:

1. Load the field “_attachments” from your kobo form:
   
2. Expand the records to get the details, including correct UUID:
   
3. Save the images as 30k packages per cell (to put them together again in the report, using DAX formulas). A lot of requirements are defined by the design of the rest of the modelling, but maybe the following can stimulate thoughts, if you are looking for similar solutions.
   let
   Source = GetKoboData(“…”),
   #“RecordDetails Rename” = Table.RenameColumns(Source, {{“Column1”, “RecordDetails”}}),
   #“Expanded UnlimitedWithFormula” = Table.ExpandRecordColumn(#“RecordDetails Rename”,“RecordDetails”,
   Table.ToList(Table.SelectColumns(Record.ToTable(Record.Combine(List.Select(Table.Column(#“RecordDetails Rename”,“RecordDetails”), each _ <> “” and _ <> null))), “Name”)),
   Table.ToList(Table.SelectColumns(Record.ToTable(Record.Combine(List.Select(Table.Column(#“RecordDetails Rename”,“RecordDetails”), each _ <> “” and _ <> null))), “Name”))
   ),
   #“Removed Other Columns” = Table.SelectColumns(#“Expanded UnlimitedWithFormula”,{“_id”, “_attachments”, “sitevisit_form/inst_centre/centre_photo1”, “sitevisit_form/inst_centre/centre_photo2”}),
   #“Expanded _attachments” = Table.ExpandListColumn(#“Removed Other Columns”, “_attachments”),
   #“Expanded _attachments1” = Table.ExpandRecordColumn(#“Expanded _attachments”, "attachments", {“download_url”, “filename”}, {“download_url”, “filename”}),
   #“Split Column by Delimiter” = Table.SplitColumn(#"Expanded attachments1", “filename”, Splitter.SplitTextByEachDelimiter({“/”}, QuoteStyle.Csv, true), {“filename.1”, “filename”}),
   #“Filtered Rows” = Table.SelectRows(#“Split Column by Delimiter”, each [filename] <> null and [filename] <> “”),
   #“Removed Columns1” = Table.RemoveColumns(#“Filtered Rows”,{“filename.1”}),
   #“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Removed Columns1”, {"id", “download_url”, “filename”}, “Attribute”, “Value”),
   #“Added Conditional Column” = Table.AddColumn(#“Unpivoted Other Columns”, “KeepRow”, each if [filename] = [Value] then true else false, Logical.Type),
   #“Filtered Rows1” = Table.SelectRows(#“Added Conditional Column”, each ([KeepRow] = true)),
   #“Removed Columns” = Table.RemoveColumns(#“Filtered Rows1”,{“KeepRow”}),
   #“Duplicated Column” = Table.DuplicateColumn(#“Removed Columns”, “download_url”, “download_url2”),
   // Relative Path in next step needed. Otherwise, dynamic source error appears
   #“Split column by delimiter 1” = Table.SplitColumn(#“Duplicated Column”, “download_url2”, Splitter.SplitTextByEachDelimiter({KoboLogin}, QuoteStyle.Csv), {“ImageURL.1”, “ImageURL”}),
   #“Get Content” = Table.AddColumn(#“Split column by delimiter 1”, “Content”, each Web.Contents(KoboImageURL,
   [RelativePath=“/media/” & SelectedPhotoSize{0} & “?media_file=” & KoboLogin & [ImageURL],
   ManualStatusHandling={404}])),
   Filesize = Table.AddColumn(#“Get Content”, “Filesize”, each Binary.Length([Content]), Int64.Type),
   #“Filtered Filesize” = Table.SelectRows(Filesize, each [Filesize] > 1000),
   #“Renamed Columns” = Table.RenameColumns(#“Filtered Filesize”,{{“Value”, “Name”}}),
   #“Removed Other Columns1” = Table.SelectColumns(#“Renamed Columns”,{“Content”, “Name”, “download_url”, “Attribute”, “Filesize”}),
   //Creates Splitter function
   SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000),
   //Converts table of files to list
   ListInput = Table.ToRows(#“Removed Other Columns1”),
   //Function to convert binary of photo to multiple
   //text values
   ConvertOneFile = (InputRow as list) =>
   let
   BinaryIn = InputRow{0},
   FileName = InputRow{1},
   Field2 = InputRow{2},
   Field3 = InputRow{3},
   BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64),
   SplitUpText = SplitTextFunction(BinaryText),
   AddFileName = List.Transform(SplitUpText, each {FileName,Field2,Field3,})
   in
   AddFileName,
   //Loops over all photos and calls the above function
   ConvertAllFiles = List.Transform(ListInput, each ConvertOneFile()),
   //Combines lists together
   CombineLists = List.Combine(ConvertAllFiles),
   //Converts results to table
   ToTable = #table(type table[Name=text,URL=text,KoboField=text,Pic=text],CombineLists),
   //Adds index column to output table
   AddIndexColumn = Table.AddIndexColumn(ToTable, “Index”, 0, 1),
   #“Added custom” = Table.AddColumn(AddIndexColumn, “photo_date”, each #datetimezone(1970,1,1,0,0,0,0,0) + #duration(0,0,0,Number.From(Text.Start([Name],10)))),
   #“Changed column type” = Table.TransformColumnTypes(#“Added custom”, {{“photo_date”, type datetimezone}}),
   #“Calculated date” = Table.TransformColumns(#“Changed column type”, {{“photo_date”, each DateTime.Date(), type nullable date}}),
   #“Replaced errors” = Table.ReplaceErrorValues(#“Calculated date”, {{“photo_date”, #date(1970, 1, 1)}})
   in
   #“Replaced errors”

[ivanradisson]

Hey @york_rff

The solution you offered requires the form submissions to be publicly visible, correct?

[york_rff]

I believe, yes, that was the issue.