View images in Power BI

[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.