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' 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



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

     

     

     

     

     

3 Replies

  • p45cal's avatar
    p45cal
    Solution Supplier
    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