Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Helper IV
1 year ago
Solved

Flag a Date Based On 2 Column Values

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...
Need Help
  • p45cal's avatar
    p45cal
    1 year ago

    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's avatar
dharmendars007
Memorable Member
1 year ago

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's avatar
    Richard_Halsall
    Helper IV
    1 year ago

    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