Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Junaid11
Helper V
Helper V

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 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:

DateIDWeek Number
3-Jul-23ABC27
3-Jul-23DEF27
3-Jul-23GHI27
10-Jul-23ABC28
10-Jul-23GHI28
17-Jul-23ABC29
17-Jul-23DEF29
17-Jul-23GHI29
17-Jul-23JKL29


Below is the outcome I am looking to have:

DateIDWeek NumberOutcome
3-Jul-23ABC27Start
3-Jul-23DEF27Start
3-Jul-23GHI27Start
10-Jul-23ABC28Yes
10-Jul-23GHI28Yes
17-Jul-23ABC29Yes
17-Jul-23DEF29Yes
17-Jul-23GHI29Yes
17-Jul-23JKL29Not in Previous Week

 

Your help would be highly appreciated.
Thank you

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@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_0-1690682358502.png

 

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

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:

vcgaomsft_0-1690767417072.png

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
Super User
Super User

@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_0-1690682358502.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.