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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.