Reference Value @ Same Power Query

[kent-culpepper]

I have a fairly intensive Power Query\ query, i.e. lots of transformation..

I want to reference a value within that same query, e.g. a prior quarter value.

I am currently duplicating the query and then merging-- but this is hurting the refresh performance.

Is there a way to "lookup", or reference, within the same query w/o duplicating\ merging?

[kent-culpepper]

[v-yiruan-msft · Accepted solution]

Hi @kent-culpepper ,

You can create a calculated column or measure as below to get it, please find the details in the attachment.

Create a calculated column:

FMV Prior = 
CALCULATE (
    MAX ( 'Table'[FMV] ),
    FILTER (
        'Table',
        'Table'[Investment] = EARLIER ( 'Table'[Investment] )
            && 'Table'[Quarter]
                = EARLIER ( 'Table'[Quarter] ) - 1
    )
)

Create a measure:

Measure = 
VAR _investment =
    SELECTEDVALUE ( 'Table'[Investment] )
VAR _qtr =
    SELECTEDVALUE ( 'Table'[Quarter] )
RETURN
    CALCULATE (
        MAX ( 'Table'[FMV] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Investment] = _investment
                && 'Table'[Quarter] = _qtr - 1
        )
    )

In addition, you can refer the following links to achieve it in Power Query Editor.

Value from previous row – Power Query, M language – Trainings, consultancy, tutorials

Fast and easy way to reference previous or next rows in Power Query or Power BI

Best Regards

[KNP]

Yes, possibly, but you'll need to provide some existing code and explain more specifically what you're trying to do.