Regarding Step 2, I don't have the keywords column created, it must be created from, I'm…
- User
Regarding Step 2, I don't have the keywords column created, it must be created from, I'm assuming Power Query/DAX, from within Power BI. However, the data is such that the same product appear in multiple rows (different batch numbers) so I must somehow find all unique values, then write a suitable set of keywords and then apply it to every row where product matches the given keywords
Assistant
Here’s a detailed step-by-step guide to create a keywords column for your products in Power Query (M language) and use it in Power BI for search functionality:
Step 1: Create a Keywords Column in Power Query
Assuming your data has a
ProductIDcolumn and other columns (e.g.,ProductName,Description, etc.) that can be used to derive keywords.Steps:
-
Load Data into Power Query:
- Open your dataset in Power Query Editor (via
Home > Transform Data). - Ensure the data includes a unique identifier for products (e.g.,
ProductID).
- Open your dataset in Power Query Editor (via
-
Extract Keywords from Existing Columns:
-
Combine Text Columns:
Create a new column that combines text fields (e.g.,ProductNameandDescription) into a single string.= Text.Combine({[ProductName], [Description]}, " ")Name this column
CombinedText. -
Split Text into Keywords:
Split the combined text into individual words.= Text.Split([CombinedText], " ")Name this column
KeywordsList. -
Clean Keywords (Optional):
Remove duplicates and irrelevant words (e.g., "the", "and") usingList.DistinctandList.Select.= List.Distinct(List.Select([KeywordsList], each _ not in {"the", "and", "a"}))Name this column
CleanKeywords.
-
-
Aggregate Keywords by Product:
-
Group Rows by ProductID:
Right-click theProductIDcolumn > Group By.- Group by:
ProductID - Aggregation:
- New column name:
AggregatedKeywords - Operation:
All Rows(or use custom aggregation).
- New column name:
- Group by:
-
Combine Keywords Across Rows:
For each product, combine all keywords from its rows into a single list.= List.Distinct(List.Combine([AggregatedKeywords][CleanKeywords]))Name this column
AllKeywords. -
Convert List to Comma-Separated String:
= Text.Combine([AllKeywords], ", ")Name this column
Keywords.
-
-
Expand Keywords Back to Original Data:
-
Merge Tables:
- Create a new table with
ProductIDandKeywordscolumns. - Merge this table back to your original data on
ProductID. - Select Left Outer Join to ensure all rows are retained.
- Create a new table with
-
Remove Unnecessary Columns:
Delete intermediate columns likeCombinedText,KeywordsList, etc., leaving onlyKeywords.
-
Step 2: Use Keywords for Search in Power BI
Once the
Keywordscolumn is added to your main table:-
Create a Search Parameter:
- Go to
Modeling > New Parameter> Text. - Name it
SearchTerm(default value: blank). - Add a slicer to your report page.
- Go to
-
Build a Filter Measure:
- Create a DAX measure to filter rows based on the search term:
Filter_Keyword = VAR SearchTerm = LOWER('SearchTerm'[SearchTerm]) RETURN IF( ISBLANK(SearchTerm), 1, // Show all if no search term IF( CONTAINSSTRING(LOWER('YourTable'[Keywords]), SearchTerm), 1, 0 ) )
- Create a DAX measure to filter rows based on the search term:
-
Apply the Filter:
- Drag the
Filter_Keywordmeasure to the Filters pane of your visual. - Set the filter condition to
= 1.
- Drag the
Step 3: Add Images (Local or Web URLs)
Option A: Use Web URLs (Recommended for Sharing)
-
Add a URL Column to Your Data:
Add a column in Excel/Power Query with the web URL of each product’s image (e.g.,https://example.com/image1.jpg). -
Set Data Category in Power BI:
- In Power BI’s Data view, select the URL column.
- Go to
Column Tools > Data Category > Image URL.
-
Display Images:
- Use the Image visual or a Card visual to show images.
- Drag the URL column to the Image URL field.
Option B: Base64 Encoding (Offline Use Only)
-
Convert Images to Base64:
Use an online converter or script to encode images into Base64 strings.
Example format:data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAA... -
Add Base64 Column to Data:
Add a column with the Base64 strings in Excel/Power Query. -
Create a Calculated Column in Power BI:
ImageURL = "data:image/png;base64," & 'YourTable'[Base64Column] -
Set Data Category:
- Select the
ImageURLcolumn >Data Category > Image URL.
- Select the
Final Notes
- Performance: If your dataset is large, ensure you optimize Power Query steps and use calculated columns wisely.
- Keyword Refinement: Use Power Query’s
Text.SplitandList functionsto refine keywords (e.g., remove stop words, standardize casing). - Sharing: Always use web URLs for images in published reports; local paths won’t work in the Power BI Service.
This setup ensures every product row has consistent keywords, enabling robust search functionality while linking to images.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.