Pivot the column in Power Query based on condition

[Anonymous]

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.

[Anonymous · Accepted solution]

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.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523