cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

 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

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
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}}),
IDData = [Data],
CustomOutcome = Table.AddColumn(AddIndex, "Outcome", each if [Index] = 0 then "Start" else let
CurrentWeek = [Week Number],
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

Super User

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