Solved: Custom Column based on multiple conditions Power Q...

Junaid11 · ‎07-28-2023

Hello,
I have 3 columns namely Date, ID, and Week Number coming from same date. I want to create two custom columns in power query based on comparing id from previous or next week.

For Previous Week column I want if the id is in first time in the week the previous week column should say "Start", If it 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"

Next Week Column will also work the same way but will be comparing with next week. If the id is in next week then the column will say "No" but if Id is not in next week then it will say "Yes". If the week ends tand there are no more weeks then it will show No I am talking about consceutive week mentioning the next week. l

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	Previous Week	Next Week
3-Jul-23	ABC	27	Start	No
3-Jul-23	DEF	27	Start	Yes
3-Jul-23	GHI	27	Start	No
10-Jul-23	ABC	28	Yes	No
10-Jul-23	GHI	28	Yes	No
17-Jul-23	ABC	29	Yes	No
17-Jul-23	DEF	29	Yes	No
17-Jul-23	GHI	29	Yes	No
17-Jul-23	JKL	29	Not in Previous Week	No

Your help would be highly appreciated.
Thank you

Anonymous · ‎07-31-2023

Hi @Junaid11 ,

Please refer to the following 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, "Previous Week", 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),
    #"Expanded Outcome" = Table.ExpandTableColumn(AddCustomColumn, "Outcome", {"Date", "Week Number", "Previous Week"}, {"Date", "Week Number", "Previous Week"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Outcome",{"Data"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Next Week", each let
id = [ID],
index = [Index],
result = try (if Table.SelectRows(#"Added Index",each [Index]=index+1)[ID]{0} = id and Table.SelectRows(#"Added Index",each [Index]=index+1)[Previous Week]{0} = "Not in Previous Week" then "Yes" else "No") otherwise "No"
in 
result),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns1"

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

View solution in original post

smozgur · ‎07-31-2023

An alternative may be. Comments included.

let 
    // Sample binary data - change with your real source
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtb1Ks3RNTJW0lFydHIGkkbmSrE6KOIurm5Yxd09PBHihgYYBllgSEB1QCXMMXRYYkhA7caUgBqFKeHl7QOViAUA", BinaryEncoding.Base64), Compression.Deflate)), {"Date", "ID", "Week Number"}),
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", type text}, {"Week Number", Int64.Type}}),
    // Original row order to be used in the last step
    AddIndex = Table.AddIndexColumn(ChangeTypes, "Index"),
    // The first week number to identify the Start value
    Start = List.Min(AddIndex[#"Week Number"]),
    // Group IDs and keep ID related rows in separate rows to process them separately
    GroupIDs = Table.Group(AddIndex, {"ID"}, {{"Data", each _}}),
    // Analyze data to define the Previous and Next week values 
    Transform = Table.TransformColumns(GroupIDs,
            {
                "Data", 
                (group) => let
                        // GroupIndex is necessary to to find out Previous Week result
                        AddIndex = Table.AddIndexColumn(group, "GroupIndex"),
                        // If the Week Number is equal to minimum week number than it is "Start"
                        // If GroupIndex is greater than 0 then it is "Yes" - it means there is a previous week with 0 index
                        // Otherwise it is 0, and since it is not Start, it means there is no previous week  
                        AddPrevious = Table.AddColumn(AddIndex, "Previous Week", 
                                    each if [Week Number] = Start then "Start" else if [GroupIndex] > 0 then "Yes" else "Not In Previous"),
                        // Adding a custom column to bring the next week number
                        ReferNext = Table.FromColumns(Table.ToColumns(AddPrevious) & {List.RemoveFirstN(AddPrevious[Week Number],1) & {null}},Table.ColumnNames(AddPrevious) & {"Next"}),
                        // If the difference between the current and next week number is 1 or null then means "No"  
                        AddNext = Table.AddColumn(ReferNext, "Next Week", 
                                    each if List.Contains({1, null}, [Next] - [Week Number]) then "No" else "Yes")
                    in
                        AddNext
            }
        ),
    // Standard wrapping up operations below
    Expand = Table.ExpandTableColumn(Transform, "Data", {"Date", "Week Number", "Index", "Previous Week", "Next Week"}),
    Sort = Table.Sort(Expand,{{"Index", Order.Ascending}}),
    Result = Table.ReorderColumns(Table.RemoveColumns(Sort,{"Index"}), {"Date", "ID", "Week Number", "Previous Week", "Next Week"})
in
    Result