Create custom column based on two columns

[Junaid11]

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 previous week. 

I want to have an column by comparing week and id, if it is id first week then it should say "Start", If the id was in previous week I mean if it is in consecutive second week then it should say "Yes" but if that id was not in previous week then it should show "Not in previous week". We are talking about consecutive weeks here. If an ID was in 1 not in week 2 and then come back in week 3 it would be considered as "Yes"

Below is my Table:

Date	ID	Week Number
3-Jul-23	ABC	27
3-Jul-23	DEF	27
3-Jul-23	GHI	27
10-Jul-23	ABC	28
10-Jul-23	GHI	28
17-Jul-23	ABC	29
17-Jul-23	DEF	29
17-Jul-23	GHI	29
17-Jul-23	JKL	29

Below is the outcome I am looking to have:

Date	ID	Week Number	Outcome
3-Jul-23	ABC	27	Start
3-Jul-23	DEF	27	Start
3-Jul-23	GHI	27	Start
10-Jul-23	ABC	28	Yes
10-Jul-23	GHI	28	Yes
17-Jul-23	ABC	29	Yes
17-Jul-23	DEF	29	Yes
17-Jul-23	GHI	29	Yes
17-Jul-23	JKL	29	Not in Previous Week

Your help would be highly appreciated.
Thank you

[v-cgao-msft]

Hi @Junaid11 ,

Steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtb1Ks3RNTJW0lFydHIGkkbmSrE6KOIurm5Yxd09PBHihgYYBllgSEB1QCXMMXRYYkhA7caUgBqFKeHl7QOViAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, #"Week Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", type text}, {"Week Number", Int64.Type}}),
    SortedData = Table.Sort(#"Changed Type",{{"Week Number", Order.Ascending}, {"ID", Order.Ascending}}),
    GroupedData = Table.Group(SortedData, {"ID"}, {{"Data", each _, type table}}),
    AddCustomColumn = Table.AddColumn(GroupedData, "Outcome", each let
        IDData = [Data],
        AddIndex = Table.AddIndexColumn(IDData, "Index", 0, 1),
        CustomOutcome = Table.AddColumn(AddIndex, "Outcome", each if [Index] = 0 then "Start" else let
            CurrentWeek = [Week Number],
            PreviousRow = AddIndex{[Index] - 1},
            PreviousWeek = PreviousRow[Week Number]
        in
            if CurrentWeek - PreviousWeek <= 1 then "Yes" else "Not in Previous Week"),
        RemoveIndex = Table.RemoveColumns(CustomOutcome,{"Index"})
    in
        RemoveIndex),
    ExpandedData = Table.ExpandTableColumn(AddCustomColumn, "Outcome", {"Date", "Week Number", "Outcome"}, {"Date", "Week Number", "Outcome.1"}),
    #"Removed Columns" = Table.RemoveColumns(ExpandedData,{"Data"})
in
    #"Removed Columns"

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[jdbuchanan71 · Accepted solution]

@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"
)