Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to Solution.
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"
)
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
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"
)
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
92 | |
91 | |
84 | |
80 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |