"Computer Vision algorithm can be used to analyze an image and output tags based on the objects, living beings, and actions identified in the image. Tagging is not limited to the main subject, such as a person in the foreground, but also includes the setting (indoor or outdoor), furniture, tools, plants, animals, accessories, gadgets, and so on."
We will use the AI Vision Insights available in Power BI to analyze an image to identify the objects within the image, and then we can perform Power BI analytics (filters, grouping, ..) on the extracted tag values.
This article has four main sections with step-by-step instructions, each with their separate blog post.
- Parse and extract Image URLs from an image gallery website, using smart data extraction algorithms available in the Web page by example feature.
- Perform AI Vision Insights transformation to analyze and identify objects from the Image URLs, also known as image tag extraction. Note: Using the Text Analytics or Vision features requires Power BI Premium.
- Create DAX measures to change the default user interface behavior of what is shown in the matrix visual when a row is expanded or collapsed.
- Visuals Comparison of the matrix visual with or without DAX measures created in the previous step.
Step A: Parse and Extract Image URLs
- You need to first determine the URL to navigate through the gallery pages.
e.g.: https://picsum.photos/images#1 , the 1 is the page number.
- Open Power BI Desktop
- Ensure you are signed in the Power BI Premium.
This will be required in Step B, when you will be using AI Vision transformations.
- Select Get Data -> Web
- Paste https://picsum.photos/images#1 and click OK.
- Click on “Add Table using Examples”
- Rename existing column to PicsumURL, and add a new column by clicking on the “+” button, and then renaming column to UnsplashURL.
This gallery page has two image URLs, and we will be extracting both of them.
- In a web browser, navigate to https://picsum.photos/images#1 and note down the picsum URL links for first 4 or 5 images starting from left to right, and then top to bottom.
- Going back to the “Add Table Using Examples” screen, start selecting the URL from the dropdown list under the PicsumURL column that corresponds to the order of appearance noted down in previous step.
Observe: The smart data extraction algorithm can easily extract the relevant data from the HTML code of the web page.
Note: sometimes only some entries are prepopulated, you will need to manually enter some more entries after prepopulated entries.
- Repeat previous two steps, but this time for the UnsplashURL, and then click OK.
- Click on Transform Data
- Add a new custom column, UnsplashDownloadURL which append “/download” to the end of UnsplashURL field. This is because the /download link take you directly to the image.
Custom column formula is:
= Text.Combine({[UnsplashURL],"/download"})
Note: the download word must be lowercase.
- Rename [Table 3] to [PicsumGalleryTemplate].
Next we will convert this table to a function so that it takes the page number as an input parameter, to fetch the image URLs for that page.
- Right-click the PicsumGalleryTemplate table, select Create Function, and name the function fx_PicsumGallery.
- Select the function -->Advanced Editor, and then make following code changes to parameterize the function.
Text.Combine({"https://picsum.photos/images#",PageNumber})
- Manage Parameters--> New --> Create a Parameter called ImageColumnName with following settings.
We will use this parameter to determine which image URL column to be used for the vision processing.
- Disable the load of PicsumGalleryTemplate table. This table was mainly used to help us create the function.
In the next few steps, we will create a blank query then we will use a list of numbers as the page numbers and then invoke the function for each page number. The function will retrieve the image URLs from each gallery page.
- Create a Blank Query
- Rename the query to images and paste the following in the formula bar.
= List.Generate(() => 1, each _ <= 2, each _ +1)
This generates a list of number starting from 1 through 2 and incrementing the next number by 1. This list of numbers will be used as our initial data source for the rest of the Power Query transformation.
- Convert the list to a table, select default settings and select OK.
- Rename the column to PageNumber.
- Change the data type of PageNumber to text.
- Invoke a custom function to generate the image gallery page URLs based on the page number.
- Expand the result of the function invocation, and select the columns from the function results.
- Add filter to only keep unsplash.com URLs.
- Add Index Column starting from 1.
- Rename the index column to ImageId.
- Move the ImageId column to be the first column.
- Duplicate one of image URL columns and modify formula bar
Source column is the Parameter value: Text.From(ImageColumnName)
Target column is: ImageURL.
- This time, duplicate the ImageURL column and rename the duplicated column in the Formula bar to WebURL.
Note: We have two columns with the same URL so that we can have both data classification representation: Web URL and Image URL for the same URL for Power BI visualization purpose.
- Delete the columns "PageNumber", "PicsumURL", "UnsplashDownloadURL"
- Your final result of the Images table will look like this.
The Complete Power Query script for the fx_PicsumGallery function is
let
Source = (PageNumber as text) => let
Source = Web.BrowserContents( Text.Combine({"https://picsum.photos/images#",PageNumber}) ),
#"Extracted Table From Html" = Html.Table(Source, {{"PicsumURL", ".download-url", each [Attributes][href]?}, {"UnsplashURL", ".author-url", each [Attributes][href]?}}, [RowSelector=".lg\:w-1\/3"]),
#"Added Custom" = Table.AddColumn(#"Extracted Table From Html", "UnsplashDownloadURL", each Text.Combine({[UnsplashURL],"/download"}))
in
#"Added Custom"
in
Source
The Complete PowerQuery script for the Images Table is
let
Source = List.Generate(() => 1, each _ <= 2, each _ +1),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "PageNumber"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "GalleryURL", each Text.Combine({"https://picsum.photos/images#",[PageNumber]})),
#"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "fx_PicsumGallery", each fx_PicsumGallery([PageNumber])),
#"Expanded fx_PicsumGallery" = Table.ExpandTableColumn(#"Invoked Custom Function", "fx_PicsumGallery", {"PicsumURL", "UnsplashDownloadURL", "UnsplashURL"}, {"PicsumURL", "UnsplashDownloadURL", "UnsplashURL"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded fx_PicsumGallery", each Text.Contains([UnsplashURL], "unsplash.com")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "ImageId", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"ImageId", "PageNumber", "GalleryURL", "PicsumURL", "UnsplashURL"}),
#"Duplicated to ImageURL" = Table.DuplicateColumn(#"Reordered Columns", Text.From(ImageColumnName), "ImageURL"),
#"Duplicated to WebURL" = Table.DuplicateColumn(#"Duplicated to ImageURL", "ImageURL", "WebURL"),
#"Removed Columns" = Table.RemoveColumns(#"Duplicated to WebURL",{"GalleryURL", "UnsplashDownloadURL", "PicsumURL", "UnsplashURL", "PageNumber"})
in
#"Removed Columns"
Summary
We covered how to use the Webpage by Example to extract image URLs, how to create a function, and invoke the function as column output.
In the next post we will look at applying AI Vision transformation to identify objects in the image.
Updated Jul 19, 2022
Version 1.0
jameelahmeds
Microsoft
Microsoft
