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