Forum Discussion

Junaid11's avatar
Junaid11
Helper V
3 years ago
Solved

Create custom column based on two columns

Hello, I have 3 columns namely Date, ID, and Week Number. Week number column is calculated based on date column I have. I want to create a custom column in power query based on comparing id from pre...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    3 years ago

    Junaid11 

    Not sure how to do it in PowerQuery but you could do it with a calculated column.  If I understand correctly then your example is off a bit. 
    JKL on Jul-17 should be "Start", it is the first time that ID appears.
    DEF on Jul-17 should be "Not in previous week" since it starts on Week 27 and is not in Week 28.

    Outcome = 
VAR _First = CALCULATE ( MIN ( 'Table'[Week Number] ), ALLEXCEPT ( 'Table','Table'[ID] ) )
VAR _Prior = CALCULATE ( MAX ( 'Table'[Week Number] ), ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Week Number] < EARLIER('Table'[Week Number] ) )
RETURN 
SWITCH(
    TRUE(),
    'Table'[Week Number] = _First, "Start",
    'Table'[Week Number] - 1 = _Prior, "Yes",
    "Not in previous week"
)

     

jdbuchanan71's avatar
jdbuchanan71
Super User
3 years ago

Junaid11 

Not sure how to do it in PowerQuery but you could do it with a calculated column.  If I understand correctly then your example is off a bit. 
JKL on Jul-17 should be "Start", it is the first time that ID appears.
DEF on Jul-17 should be "Not in previous week" since it starts on Week 27 and is not in Week 28.

Outcome = 
VAR _First = CALCULATE ( MIN ( 'Table'[Week Number] ), ALLEXCEPT ( 'Table','Table'[ID] ) )
VAR _Prior = CALCULATE ( MAX ( 'Table'[Week Number] ), ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Week Number] < EARLIER('Table'[Week Number] ) )
RETURN 
SWITCH(
    TRUE(),
    'Table'[Week Number] = _First, "Start",
    'Table'[Week Number] - 1 = _Prior, "Yes",
    "Not in previous week"
)