Forum Discussion
Reference Value @ Same Power Query
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?
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
3 Replies
- KNP
Super User
Yes, possibly, but you'll need to provide some existing code and explain more specifically what you're trying to do.
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
