Forum Discussion
Solved
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 pre...
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