View images in Power BI

User Support Data Management
,
31

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”
1 Like