Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Pivot the column in Power Query based on condition

Hi,

I have the following data in power Query

I want to transform it to the following:

 

Following logic to be used:

1. First message for the contact_id of the day with interaction as incoming should be moved to Keyword field and the date should be entered in the datetime column

2. Subsequent messages of the day for that contact should be recorded in either Qn or Rn basis if the interaction is outgoing or incoming respectively.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    I have created a simple sample, please refer to it to see if it helps you.

    First, add an index column.

    Then create a column.

    result =
VAR _1 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "incoming"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index]
    )
VAR _Q1 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "outgoing"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index]
    )
VAR _R1 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "incoming"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 2
    )
VAR _Q2 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "outgoing"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 2
    )
VAR _R2 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "incoming"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 4
    )
VAR _Q3 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "outgoing"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 4
    )
VAR _R3 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "outcoming"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 6
    )
RETURN
    IF (
        _1 = 'Table'[Index],
        "keyword",
        IF (
            _Q1 = 'Table'[Index],
            "Q1",
            IF (
                _R1 = 'Table'[Index],
                "R1",
                IF (
                    _Q2 = 'Table'[Index],
                    "Q2",
                    IF (
                        _R2 = 'Table'[Index],
                        "R2",
                        IF ( _Q3 = 'Table'[Index], "Q3", IF ( _R3 = 'Table'[Index], "R3", BLANK () ) )
                    )
                )
            )
        )
    )

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    I have created a simple sample, please refer to it to see if it helps you.

    First, add an index column.

    Then create a column.

    result =
VAR _1 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "incoming"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index]
    )
VAR _Q1 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "outgoing"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index]
    )
VAR _R1 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "incoming"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 2
    )
VAR _Q2 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "outgoing"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 2
    )
VAR _R2 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "incoming"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 4
    )
VAR _Q3 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "outgoing"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 4
    )
VAR _R3 =
    MINX (
        FILTER (
            'Table',
            'Table'[interaction] = "outcoming"
                && 'Table'[contact_id] = EARLIER ( 'Table'[contact_id] )
                && 'Table'[datetime] = EARLIER ( 'Table'[datetime] )
        ),
        'Table'[Index] + 6
    )
RETURN
    IF (
        _1 = 'Table'[Index],
        "keyword",
        IF (
            _Q1 = 'Table'[Index],
            "Q1",
            IF (
                _R1 = 'Table'[Index],
                "R1",
                IF (
                    _Q2 = 'Table'[Index],
                    "Q2",
                    IF (
                        _R2 = 'Table'[Index],
                        "R2",
                        IF ( _Q3 = 'Table'[Index], "Q3", IF ( _R3 = 'Table'[Index], "R3", BLANK () ) )
                    )
                )
            )
        )
    )

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.