Spreadsheet Pivot Table Joins With Key Validation and Auto Alignment

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing spreadsheet applications, except for Microsoft Excel's Power Pivot, lack the ability to effectively join different data sets within PivotTables, and Power Pivot has limitations such as failing to prevent joins with data type mismatches, not supporting multiple join types, and requiring directional alignment with PivotTable inputs, leading to incorrect or incomplete results.

Innovation Solution

The technology provides proactive and reactive measures to prevent joins that will fail due to data type mismatches, supports multiple join types including compound keys and unique-to-many relationships, and automatically adjusts PivotTable inputs to ensure correct joins, with clear error messaging.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If Power Pivot is used to join data sets, then join capability is provided, but data type mismatch errors are not prevented and incorrect results may occur

Engineering Contradiction:

Improvejoin reliabilityVSAvoiddata type mismatch errors

Core Design Contradiction:

ReliabilityVSObject-affected harmful factors

Solution Approach 1:

The system performs preliminary validation of join key data types and values before executing the join operation. It checks whether the data types of join keys from different data sets are compatible and whether the join keys contain overlapping values, preventing incorrect joins from being executed in the first place.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system provides feedback to the user by displaying clear error messages when data type mismatches or lack of overlapping values are detected. This feedback mechanism informs users of the issues and prevents them from proceeding with incorrect join operations.

Inventive Principle:

Principle #23Feedback

2Adaptability or versatility

If Power Pivot is used for joins, then basic join functionality is available, but multiple join types (inner, full outer, self-joins) are not supported

Engineering Contradiction:

Improvejoin type flexibilityVSAvoidjoin configuration complexity

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The join configuration is segmented into separate, manageable components: selecting the first data set and its join key, then selecting the second data set and its join key. This segmentation allows users to configure complex joins step-by-step without being overwhelmed by the overall complexity.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The system dynamically determines the appropriate join type based on the selected data sets and join keys. It automatically identifies whether inner joins, full outer joins, or self-joins are needed based on the data characteristics and user selections, eliminating the need for manual join type specification.

Inventive Principle:

Principle #15Dynamics

3Reliability

If Power Pivot joins are configured, then data joining is enabled, but directional alignment with PivotTable inputs is required which leads to incorrect results

Engineering Contradiction:

Improvejoin result accuracyVSAvoidjoin configuration ease

Core Design Contradiction:

ReliabilityVSEase of operation

Solution Approach 1:

The system automatically determines the correct join direction based on the selected data sets and join keys, eliminating the need for users to manually specify directional alignment. The join operation self-adjusts to match the PivotTable input requirements, ensuring correct results without additional user configuration.

Inventive Principle:

Principle #25Self-service

4Adaptability or versatility

If spreadsheets lack join capability, then simplicity is maintained, but multi-data set analysis is limited

Engineering Contradiction:

Improvedata set integration capabilityVSAvoidspreadsheet functionality complexity

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The join functionality is integrated into the universal PivotTable interface, allowing users to work with multiple data sets using the same familiar operations. The join feature leverages existing PivotTable capabilities for data selection, filtering, and aggregation, extending functionality without requiring separate complex join tools.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Data Source

PatentUS20250384204A1Methods and systems for improving specifying and using joins between data sets in a spreadsheet pivot table

Publication Date: 2025.12.18 ADAPTAM INC

AI summary

The disclosed technology creates a range of manual to automated ways of setting up and verifying the correct joining of data sets for use generating spreadsheet pivot tables. Handling pairing of data sets and then chaining/sequencing of joins resulting in joins of three or more data sets. Limiting joins to keys with matching data types and matching data values. Limiting joins, as desired, to data value matching criteria and data modelling cardinality requirements. Allowing the qualification of the data sets for joining to be done within or before spreadsheet pivot input panel. Making the qualified joinable data sets available in situationally intelligent data field/range selection lists for use populating the pivot table inputs. Availing those joined data fields/ranges to be used to generate spreadsheet pivot tables.