Flag a Date Based On 2 Column Values

[Richard_Halsall]

Hi

I am struggling to resolve this requirement which in summary for each ID

I need to create a flag named 'WeeksOnsiteFlag' whose value is 'Y' for the number of days stated in the 'WeeksOnDays' column and 'N' for the number of days stated in the 'WeeksOffDays' column repeating for the all dates in the column 'HeadcountDate' ONLY if the column 'Back_to_Back' is FALSE, if 'Back_to_Back' is TRUE the 'WeeksOnsiteFlag' will always be 'Y' for all dates in the column 'HeadcountDate

So in this example there would be 28 'Y' then '14' N then 28 'Y' then '14' N etc etc until the Id changed

A sample data set is here with the expected result of the WeeksOnsiteFlag
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZrdqhVHEEbfZV8LTlfPT+/LI4lwQAMnUVDEq+Qd8vrZA7L7q3RPzawWVBDX3Tq9uqfqx4/bNK1vedp/pX9/+7y+vny+vbt9fPn01++Pv608/kjz448pvZ/m9zbZsv/L4/f3289312kT2jCdhc6YnoWeMb0IvWB6FXrF9Cb0hukidMH0Xeg7pdOktkwYd7Jh25LalrBuSXVL2LekviUsXFLhEjYuqXEJK5dUuYSdS+pcwtIllS5h60ytM2ydqXWGrTN3xmHrTK0zbJ2pdYatM7XOsHWm1hm2ztQ6w9aZWmfYOlPrbLfuD4JntS5PFN/DujzxhHFT3DCeFc8YnxWfMb4ovmB8VXzF+Kb4hvGieMH4XXFs3V7YJz5j65JaN2Prklo3Y+uSWjcPFbbiQ4Wt+FBhKz5U2IoPFbbiQ4Wt+FBhn/gyVNiKDxW24kOFrfhQYSs+VNiKDxW24kOFrfhQYSs+VNiKY+uyWrdi67Jat/K36wNfnzh/vJri/PWaFefP11lx/n5dFOcP2FVx/oLdFOdP2KI4tm4P9BPf+Bt2UpwXVq3beGHVug3f65Jat+F7XVLrNnyvS2rdhu91Sa3b8L0uqXUbvtcltW7j9zq1ruB7nal1BVtnal3B1plaV7B1ptYVbJ2pdYUXVq0rvLBqXeGFVesKL6xaV/i9Tq2788Kqdfehwm5PfKiwFR8qbMWHClvxocJWfKiwFR8qbMWHClvxocI+8TQNJVZ4/p04OZ5/KDbH83dsdjx/yM6O5y/ZxfH8Kbs6nr9lN8fzx2xxPJ9SOP/4mMKcf3xOYc4/Pqgw518a6q3wQ8EVHt/zzPmX8EXPnH8J3/TM+ZfwVc+cfwnf9cz5Z/iyl51/hm972fln/OPxgy+V51+PzfH883F2PP9+PDuef0BeHM/ruzqe53dzPO9vUT7j82/vt/C8v5PjeX+df5n31/mXeX+df5n31/mXeX+df5n31/mXeX+df5n31/k38/uf82/m/XX+zby/zj8+xzDnHx9kmPOPTzLM+cdHGeb847MMc/7xYYY5/wamGc6/gXGG84/PM7Lzjw80svOPTzT2ft8r3/Mv//N3+fb68ib8r6PrdmEnLKbPdsJi+mwnLKbPdsJi+mwnLKbPdsJi+mwnLKbPdsJi+mwnLKTPd8Ji/HQnLMZPd8Ji/HQnLMZPd8JivNkJ+/89NcabnTCGNzthDG92whje7IQhvN0JY3izE8bwZieM4c1OGMObnbAWf5s/vL587eJubtr/gQtoNzbt/7xFtE4S+j9uEa2DhP5PW0TrHKF/vEe0G5li2s2uMO2GCJjWGUL/eI9oHSEcHO8B7salB8d7hKttB8d7hKtuB8d7hKtvB8d7hKtwB8d7hKtxB8d7hKtyB8d7hKtzB8d7hKt0B8d7hKt1B8d7gLtx6cHxHuHujOsf7xGu1h0c7xGu1vHj3Y1Lux/PYlyt6347i3G1zrB1blxq2Do3Lu1+OItxta773ewXvv/nL39+HetqF76a1T58sap9+GJU+/DFpvbhi0ntwxeL2ocvBrUPX+xpH76a0y59uaZ9+mpM+/TVlvbpqynt001JEd2EFNFNRxHdZBTRTUUR3USU0G1DEd0kFNFNQRHdBBTRTT8R3eQT0U09Ed3EE9FX29mne+n8+R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Back_to_Back = _t, WeeksOnDays = _t, WeeksOffDays = _t, HeadcountDate = _t, DayCount = _t, WeeksOnsiteFlag = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Back_to_Back", type logical}, {"WeeksOnDays", Int64.Type}, {"WeeksOffDays", Int64.Type}, {"HeadcountDate", type date}, {"DayCount", Int64.Type}, {"WeeksOnsiteFlag", type text}})
in
#"Changed Type"

Any help would be much appreciated. Thanks

[p45cal · Accepted solution]

Edit: I think I've got it slightly wrong but out of time, will come back in a few hours. I have corrected this below now.

Is this right?: add one step to your query:

= Table.AddColumn(#"Changed Type", "WeeksOnsiteFlag2", each if [Back_to_Back] = true or Number.Mod([DayCount]-1,[WeeksOnDays]+[WeeksOffDays])+1 <= [WeeksOnDays] then "Y" else "N")

In full:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZrdqhVHEEbfZV8LTlfPT+/LI4lwQAMnUVDEq+Qd8vrZA7L7q3RPzawWVBDX3Tq9uqfqx4/bNK1vedp/pX9/+7y+vny+vbt9fPn01++Pv608/kjz448pvZ/m9zbZsv/L4/f3289312kT2jCdhc6YnoWeMb0IvWB6FXrF9Cb0hukidMH0Xeg7pdOktkwYd7Jh25LalrBuSXVL2LekviUsXFLhEjYuqXEJK5dUuYSdS+pcwtIllS5h60ytM2ydqXWGrTN3xmHrTK0zbJ2pdYatM7XOsHWm1hm2ztQ6w9aZWmfYOlPrbLfuD4JntS5PFN/DujzxhHFT3DCeFc8YnxWfMb4ovmB8VXzF+Kb4hvGieMH4XXFs3V7YJz5j65JaN2Prklo3Y+uSWjcPFbbiQ4Wt+FBhKz5U2IoPFbbiQ4Wt+FBhn/gyVNiKDxW24kOFrfhQYSs+VNiKDxW24kOFrfhQYSs+VNiKY+uyWrdi67Jat/K36wNfnzh/vJri/PWaFefP11lx/n5dFOcP2FVx/oLdFOdP2KI4tm4P9BPf+Bt2UpwXVq3beGHVug3f65Jat+F7XVLrNnyvS2rdhu91Sa3b8L0uqXUbvtcltW7j9zq1ruB7nal1BVtnal3B1plaV7B1ptYVbJ2pdYUXVq0rvLBqXeGFVesKL6xaV/i9Tq2788Kqdfehwm5PfKiwFR8qbMWHClvxocJWfKiwFR8qbMWHClvxocI+8TQNJVZ4/p04OZ5/KDbH83dsdjx/yM6O5y/ZxfH8Kbs6nr9lN8fzx2xxPJ9SOP/4mMKcf3xOYc4/Pqgw518a6q3wQ8EVHt/zzPmX8EXPnH8J3/TM+ZfwVc+cfwnf9cz5Z/iyl51/hm972fln/OPxgy+V51+PzfH883F2PP9+PDuef0BeHM/ruzqe53dzPO9vUT7j82/vt/C8v5PjeX+df5n31/mXeX+df5n31/mXeX+df5n31/mXeX+df5n31/k38/uf82/m/XX+zby/zj8+xzDnHx9kmPOPTzLM+cdHGeb847MMc/7xYYY5/wamGc6/gXGG84/PM7Lzjw80svOPTzT2ft8r3/Mv//N3+fb68ib8r6PrdmEnLKbPdsJi+mwnLKbPdsJi+mwnLKbPdsJi+mwnLKbPdsJi+mwnLKTPd8Ji/HQnLMZPd8Ji/HQnLMZPd8JivNkJ+/89NcabnTCGNzthDG92whje7IQhvN0JY3izE8bwZieM4c1OGMObnbAWf5s/vL587eJubtr/gQtoNzbt/7xFtE4S+j9uEa2DhP5PW0TrHKF/vEe0G5li2s2uMO2GCJjWGUL/eI9oHSEcHO8B7salB8d7hKttB8d7hKtuB8d7hKtvB8d7hKtwB8d7hKtxB8d7hKtyB8d7hKtzB8d7hKt0B8d7hKt1B8d7gLtx6cHxHuHujOsf7xGu1h0c7xGu1vHj3Y1Lux/PYlyt6347i3G1zrB1blxq2Do3Lu1+OItxta773ewXvv/nL39+HetqF76a1T58sap9+GJU+/DFpvbhi0ntwxeL2ocvBrUPX+xpH76a0y59uaZ9+mpM+/TVlvbpqynt001JEd2EFNFNRxHdZBTRTUUR3USU0G1DEd0kFNFNQRHdBBTRTT8R3eQT0U09Ed3EE9FX29mne+n8+R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Back_to_Back = _t, WeeksOnDays = _t, WeeksOffDays = _t, HeadcountDate = _t, DayCount = _t, WeeksOnsiteFlag = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Back_to_Back", type logical}, {"WeeksOnDays", Int64.Type}, {"WeeksOffDays", Int64.Type}, {"HeadcountDate", type date}, {"DayCount", Int64.Type}, {"WeeksOnsiteFlag", type text}}),
    AddedCustom = Table.AddColumn(#"Changed Type", "WeeksOnsiteFlag2", each if [Back_to_Back] = true or Number.Mod([DayCount]-1,[WeeksOnDays]+[WeeksOffDays])+1 <= [WeeksOnDays] then "Y" else "N")
in
    AddedCustom

[dharmendars007]

Hello @Richard_Halsall , 

We can achieve this in Power Query by:

1. Grouping the data by ID to ensure the logic resets per ID.
2. Adding a custom column that calculates the WeekOnsiteFlag based on Back to Back and the alternating pattern.

let
// Load the data
Source = <Your Data Here>,

// Step 1: Group data by ID to handle each ID separately
GroupedData = Table.Group(Source, {"ID"}, {
{"AllRows", each _}
}),

// Step 2: Create a function to generate the WeekOnsiteFlag column for each group
GenerateFlag = (TableForID) =>
let
IsBackToBack = TableForID{0}[Back to Back], // Check if 'Back to Back' is TRUE
DaysOn = TableForID{0}[WeekOnDays], // Number of 'Y' days
DaysOff = TableForID{0}[WeekOffDays], // Number of 'N' days

// Logic to generate flags based on Back to Back condition
AddFlag = if IsBackToBack then
Table.AddColumn(TableForID, "WeekOnsiteFlag", each DaysOn & " Y")
else
let
// Create alternating "Y" and "N" based on days
AddIndex = Table.AddIndexColumn(TableForID, "Index", 0, 1, Int64.Type),
AddFlagColumn = Table.AddColumn(AddIndex, "WeekOnsiteFlag",
each if Number.Mod([Index], 2) = 0
then DaysOn & " Y"
else DaysOff & " N")
in
Table.RemoveColumns(AddFlagColumn, {"Index"})
in
AddFlag,

// Step 3: Apply the function to each group
AddFlagToGrouped = Table.TransformColumns(GroupedData, {"AllRows", each GenerateFlag(_)}),

// Step 4: Expand the grouped data back into a flat table
ExpandedTable = Table.ExpandTableColumn(AddFlagToGrouped, "AllRows")
in
ExpandedTable

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Richard_Halsall]

Hello @dharmendars007 many thanks for your quick reply unfortunately after pasting the code it is showing an error at the 'AddFlagToGrouped' step

Are you able to advise? Thanks