View images in Power BI

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

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

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

1 Like

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

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

…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?

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

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

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?

image

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.

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?

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!

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:
    image
  2. Expand the records to get the details, including correct UUID:
    image
  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”

Hey @york_rff

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