Combine Similar Matching Rows Into One and Add Column Using Power Query

Take this:

Step 1: Load and Clean Both Sources

• Load both CSV and XLSX into Power Query.
• Apply your existing transformations (trimming, cleaning, etc.).

Step 2: Use Fuzzy Merge

If the Asset values are similar but not identical:

• Use Fuzzy Merge between CSV and XLSX on Asset and Path.
• Enable Fuzzy Matching and adjust the similarity threshold.
• This helps match truncated or dirty Asset names.

Step 3: Group and Aggregate

1. In the CSV query:
• Go to Transform > Group By
• Group by Path and a cleaned version of Asset (e.g., first 35 characters or a normalized key)
• Use Sum on Hits
2. You can also create a custom grouping key:

3. Text.Lower(Text.Start([Asset], 35))

This helps group similar truncated assets.

Step 4: Merge with XLSX

• Merge the grouped CSV with the XLSX query on Path.
• Expand Title and other columns as needed.

Step 5: Final Cleanup

• Remove intermediate columns (like grouping keys).
• Rename columns for clarity.
• Ensure no duplicates remain.