Skip to content
TheChatArchiveThe Chat Archive
Qwenqwq-32b· Alibaba (Qwen)Hugging Face LMArena Human Preference 140K

Regarding Step 2, I don't have the keywords column created, it must be created from, I'm…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-3-5-sonnet-20241022’s answer.
  1. 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

  2. Qwen 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 ProductID column and other columns (e.g., ProductName, Description, etc.) that can be used to derive keywords.

    Steps:

    1. 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).
    2. Extract Keywords from Existing Columns:

      • Combine Text Columns:
        Create a new column that combines text fields (e.g., ProductName and Description) 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") using List.Distinct and List.Select.

        = List.Distinct(List.Select([KeywordsList], each _ not in {"the", "and", "a"}))
        

        Name this column CleanKeywords.

    3. Aggregate Keywords by Product:

      • Group Rows by ProductID:
        Right-click the ProductID column > Group By.

        • Group by: ProductID
        • Aggregation:
          • New column name: AggregatedKeywords
          • Operation: All Rows (or use custom aggregation).
      • 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.

    4. Expand Keywords Back to Original Data:

      • Merge Tables:

        • Create a new table with ProductID and Keywords columns.
        • Merge this table back to your original data on ProductID.
        • Select Left Outer Join to ensure all rows are retained.
      • Remove Unnecessary Columns:
        Delete intermediate columns like CombinedText, KeywordsList, etc., leaving only Keywords.


    Step 2: Use Keywords for Search in Power BI

    Once the Keywords column is added to your main table:

    1. Create a Search Parameter:

      • Go to Modeling > New Parameter > Text.
      • Name it SearchTerm (default value: blank).
      • Add a slicer to your report page.
    2. 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
            )
        )
        
    3. Apply the Filter:

      • Drag the Filter_Keyword measure to the Filters pane of your visual.
      • Set the filter condition to = 1.

    Step 3: Add Images (Local or Web URLs)

    Option A: Use Web URLs (Recommended for Sharing)

    1. 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).

    2. Set Data Category in Power BI:

      • In Power BI’s Data view, select the URL column.
      • Go to Column Tools > Data Category > Image URL.
    3. 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)

    1. Convert Images to Base64:
      Use an online converter or script to encode images into Base64 strings.
      Example format: data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAA...

    2. Add Base64 Column to Data:
      Add a column with the Base64 strings in Excel/Power Query.

    3. Create a Calculated Column in Power BI:

      ImageURL = "data:image/png;base64," & 'YourTable'[Base64Column]
      
    4. Set Data Category:

      • Select the ImageURL column > Data Category > Image URL.

    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.Split and List functions to 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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.