Forum Discussion
Anonymous
6 years agoNot applicable
Replace value based on date column
Hello, I am trying to replace all the values in column A if the date in column B is before 20-02-2020 with null. Because from that date on the values in column A are correct. Column A contains a ...
- 6 years ago
Hi Anonymous ,
The necessary replacement operation looks like so:
Table.ReplaceValue(#"Changed Type", each if Date.From([CreatedDate]) < #date(2020,2,20) then [Ga_userid__c] else null, null,Replacer.ReplaceValue,{"Ga_userid__c"})I used the technique from my blogpost here: https://www.thebiccountant.com/2017/07/23/transforming-a-column-with-values-from-another-column-in-powerbi-and-powerquery-in-excel/
This is the full code incl. sample data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVjLiiXHEf2VZlY2CCYeGfm4axs8Bm+MdkKLyMxIWSC1zWj8/z5ZdVv9qCs8DD0Mfc+tzIg4j6gffvhE9pn0s5DQE9Vbqp+++yTV5lqDlcRytEiTyHJprYVrMH368bv3uHJLBTiNGX1YKxIleWRZRSW7lBhkJa1HOAGOqkZvKXuYzalmvoauGLm23mvwiUu/44RvtHE8lth0yUudKy/z3NeMNlIqlPr6iON6o32/aU6AVqYmDY8p1gtJsxWFkxS64OymBlzl0UxHzmKoUOo05hgqvXimWuT6PLtJ3udszFrcZ+hKvWTUlFspZChOI8oXXLrxrmfPVWn1NojDu4jPWYpwt9Yls13qsnEK3PCch8gy3CenkQRdGLMaZSJNOh7hdj21jNUoSh5pcvVKLrufqVTBSDyoJ3C877fcmpfRtUimFtIzBecyRszGtTzC0caFN0anc8nGwqm61BST0X3Ae3+AowZc7nWxdzONtiJF0jxc0Qhhr6HxCLf7Z4Lbl5W0ajLpYRJtcs9dK0VLj+py4Ca65lZ1ljlKbxxoYG2FuPfcpvsf4cLyopmtgkG+uPuQ1Sqp1gC9yrWeepN9P+7Rs/aUgtuonvcFexq9dJmg0nU+5ex7nyj3MjAQYxx51IzWOy1aLCuV65zxTdL5vFbzWL5Q2FaVZcrE6DmmjnCKR7g9L/jkpJ7WaiU1Qr9dfXkzT1yTpmv/+Mb7fsUxk1SIArTdt0p91jl0ag6U614XfcW1m+w5Sw1f30eYu9rCyGyBChxQxCwxP8DRnrMiPbG2usaWi9pWzpjOaDIgbFzsgss33bylEWItIq2+Os7Y5gITSSNjzsCVCw46sftAWhNoN6tooBtsxpQc3cdAGKAXHObl4DumcHgpoBKVpgldGYRecA2vS+iC01va9wvyrMbTrJQaNi1R8ylCKj4i4oKjs+9O1YbUmpXmzFUwlkLUo/Q6O077EUft1OvQhIpDI6I2jLHJYFIe3tWyW7n3QV71Wu++gsln2lqZfdYgSDtks68yqWab9YKTm+5z9sUj+2D0evAmasV4VTeHGON/5SMOOn/4UeRZfE3wdhTG4VBJmStPWhmk6PmCy7e0eVvY0eWReOFcIVQXeMzQwNaoVLvcb+vZnmspcED8zRB88GdJ75gzVBRWkQkf+YiTs565pALWDLBpODSKJgrE8OAMNTOdD3DHnDk5BWovgzb9ki7NjWCE2l1kyAVHJ4/AnQFHZi6lVa7SOsxIF885DUy8951f+0CnH/nw6KSlDAw4tLsp/GutBY70wSt/xKGehw6u0njrrvWmLVapReHVC/KBRNGyX3B2S4dvruSUbEzB5YCjFVB6DPswDwm94PTGG+cZQo2aG6aTBezFD2IwtyJoDKsXHJ2+0hllC8Ndotc2k87ei3ZIolRt47yftM8o6Tu+41MzZkL40ZRXRgPhuugCAhQ6dL/fGxzlk7cNhiKeIWYTih9lZyxpNNDS0vmee6T+jpP7OfGpzL0gjuQW1MeGAmDWI7vfc9Yb3K7n7kMDVR2a6SnyJkVqIHkjTB30Yqx0wcnNNv9q7Z7gyZ3LtGLwCl9twJXgitDE/AB3zLWj476yBXwkC4wSJUVpdeYpXcke4HTfj5By4GRz33EU1MgW+UQ6y2nhyvoIV4886HnJUELyZBkLtXEv3eGDiFIlHuHywdtYVqHxCDB1CzREbSpmRtPWpWs977rkvcIgczDu2Y2GZl7BCpJg1lDWR7g9L4Gxxhi34uArOp5q30rWVkEnh/gj3FFPxOnqs0B8aSYaJa8k2+6RSHGSh/c78hl0nW23GBmp4Xo6veVYaRN4hTzAyeZ7gpjp5hzorpAnEKGamrQs2coqFxzfbPdhF2RCtAUEXwNSi3gA3gcbzEwfPI/O52EobPgYMrXs7IQ5n4gk1Lq34HLhw95Xdj2RFydc1sGGtjwLejIwmqthecAsXebsZV8BXxKIg68nZ3HkbIPaYPAgn7rmvQ/l9ZztzPNFXBBDZs3wycimgv9gmUhIBfhBF5zd9yOKnLEJNZUlyOdZ1ZrB2XU29Xu+fo87+AA3WmDEoNHhP3kilC1VKoKoMPllrt/g0lkXjNdMnGdLSAbQQNgJFrgB6hYtevfNtzi6pSNfL5nEqYAWMD6MDRYcCDilYnALlo845AI7eNQgIqhKoGVu2BkLVBFeMzv8ZtbL89CHI/fMuXTUlEG7xH1iS8EtnRXxGozK/YLTU18q0sRCfhtjxyNcEPsY/vEEOkH7T52X/Hq/euZB5A6MxsEkj4pFNcHccfaCxERS5wWXz30lQ6BHHwksnHvUDAsLiL46+KVttQvupX/LZu4lpVoLsvFamO5gLK64LOUX3uZ3fTjqCe8YmOSWcC+YirJrQ5KVaYIhvTwPfRA75ozR94T5mlgDUpOFGGhYlToWeNd7Pe3tXB97XEeUQGxJWMMTvDYhywOMju50DQG44OzsX521otud2bHSUodYY8VmbJrWoW7X56Wb7XruESFYgSiWAKwqOJ3Jwv6HysYo1+elmx48yljgHf4KE0mrYtXldkhGGQ1Mng9wR95FoqM2phFcmseEeiIUJOzFiMnYYfQBjs7cYzwQjit1rB6gj08sLnU7Iizmnlvf4uCbxx6wJPe9Ow7QD0kXEhoZ60cocl7u/gB3+BGOhqCcEedcsPpB3OFmEF1oYK9o/AXH5/5XRyutdSQI7B8Q3dq0ChZJGDaSqVxxdOoSU19Y/rD5IR/Z5NR1QFuWQ1GxTVzOiTk7cpYxNu5i293364WRxXJFSEZShrvf8+dbXD3nOjp0xUFWYAjalxGSeZ8Vv8BRLn1AXjr6h4XfQD4aec7U8QdEyLiqUS+cx51H6TUv6c32XGOZaaVWCHbZr4c4dE62DA8WQyTkj7i9N+7ngUbQ3FomZqPHRGISKHCFQWE9SNYuuHrTI9etCV+dlGALcy+RndfoiF5l5/QuF5zeebRfpPhC9ZTZyPezkOoNGR854SWfpXfzcvrKxLKuuBWiZAMp2t78o2eMKszpUpftt3T4Q5I2sYTt9wUIJMh2SIhwGWgThrR/xNF9r9KUFr4+DdEdzg0rdUdKcNupt7zkQX3tA599GG2hE1DqChXSIggWGHIQCyzCXMcD3JGTkfxL6dNgS1jGKqJZONYqZOAVubN+xKF/h75wckHrOBBboYd47MR0Q6nBxYDPXHD11M/9ZqIhgKLmsyI2IdshmyFAGVwTgnXBldM359rJHaaEm80YLUygnojlKEpDbLrg7vvDSHNq3XF+LGQYDEsdXiBN8LJu9/chb3H3XDdScK+IrCvGgOdm8FbH9OTIBfHCP377vOO9DbZvOAnYjUC/8+deXgzVZERSdNYvODn5B8a1KClDkZCPwIYysB7HauiowIEvODr3fkMg5r0VYYoznEwCy+nYbxlghSjQiaN3fDh0AmyLIiPvF0UpRxtYtBTLPKStzvt7DW5vcYcujQIboMYZYjZguDvZecR+JVOwOZ715Hf+bnbs4Qi8BYkDa5RtZcBjsAAiwGofGNgL7s6/NLFMj4m8qUhXRZuGY+Vo8DCd6+5j/N4393x+ecYlTrfid9N0bM1/Cf/69A9/9p/i69PPz09/++fTjP/412+/xvO37w4UPvVH/355/hZf47dvMTf2y/dP33/1n58jPv/93/3p9qcvT/7r0/rlv/iq/fu/Pv/0y8+//evpz//3a8/d+f0OvGuuptBIBGJs6g3ZlNaS/ZKFkRpL2hnnx/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CreatedDate = _t, Ga_userid__c = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CreatedDate", type datetime}, {"Ga_userid__c", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type", each if Date.From([CreatedDate]) < #date(2020,2,20) then [Ga_userid__c] else null, null,Replacer.ReplaceValue,{"Ga_userid__c"}) in #"Replaced Value"
ImkeF
6 years agoCommunity Champion
Hi Anonymous ,
The necessary replacement operation looks like so:
Table.ReplaceValue(#"Changed Type", each if Date.From([CreatedDate]) < #date(2020,2,20) then [Ga_userid__c] else null, null,Replacer.ReplaceValue,{"Ga_userid__c"})
I used the technique from my blogpost here: https://www.thebiccountant.com/2017/07/23/transforming-a-column-with-values-from-another-column-in-powerbi-and-powerquery-in-excel/
This is the full code incl. sample data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVjLiiXHEf2VZlY2CCYeGfm4axs8Bm+MdkKLyMxIWSC1zWj8/z5ZdVv9qCs8DD0Mfc+tzIg4j6gffvhE9pn0s5DQE9Vbqp+++yTV5lqDlcRytEiTyHJprYVrMH368bv3uHJLBTiNGX1YKxIleWRZRSW7lBhkJa1HOAGOqkZvKXuYzalmvoauGLm23mvwiUu/44RvtHE8lth0yUudKy/z3NeMNlIqlPr6iON6o32/aU6AVqYmDY8p1gtJsxWFkxS64OymBlzl0UxHzmKoUOo05hgqvXimWuT6PLtJ3udszFrcZ+hKvWTUlFspZChOI8oXXLrxrmfPVWn1NojDu4jPWYpwt9Yls13qsnEK3PCch8gy3CenkQRdGLMaZSJNOh7hdj21jNUoSh5pcvVKLrufqVTBSDyoJ3C877fcmpfRtUimFtIzBecyRszGtTzC0caFN0anc8nGwqm61BST0X3Ae3+AowZc7nWxdzONtiJF0jxc0Qhhr6HxCLf7Z4Lbl5W0ajLpYRJtcs9dK0VLj+py4Ca65lZ1ljlKbxxoYG2FuPfcpvsf4cLyopmtgkG+uPuQ1Sqp1gC9yrWeepN9P+7Rs/aUgtuonvcFexq9dJmg0nU+5ex7nyj3MjAQYxx51IzWOy1aLCuV65zxTdL5vFbzWL5Q2FaVZcrE6DmmjnCKR7g9L/jkpJ7WaiU1Qr9dfXkzT1yTpmv/+Mb7fsUxk1SIArTdt0p91jl0ag6U614XfcW1m+w5Sw1f30eYu9rCyGyBChxQxCwxP8DRnrMiPbG2usaWi9pWzpjOaDIgbFzsgss33bylEWItIq2+Os7Y5gITSSNjzsCVCw46sftAWhNoN6tooBtsxpQc3cdAGKAXHObl4DumcHgpoBKVpgldGYRecA2vS+iC01va9wvyrMbTrJQaNi1R8ylCKj4i4oKjs+9O1YbUmpXmzFUwlkLUo/Q6O077EUft1OvQhIpDI6I2jLHJYFIe3tWyW7n3QV71Wu++gsln2lqZfdYgSDtks68yqWab9YKTm+5z9sUj+2D0evAmasV4VTeHGON/5SMOOn/4UeRZfE3wdhTG4VBJmStPWhmk6PmCy7e0eVvY0eWReOFcIVQXeMzQwNaoVLvcb+vZnmspcED8zRB88GdJ75gzVBRWkQkf+YiTs565pALWDLBpODSKJgrE8OAMNTOdD3DHnDk5BWovgzb9ki7NjWCE2l1kyAVHJ4/AnQFHZi6lVa7SOsxIF885DUy8951f+0CnH/nw6KSlDAw4tLsp/GutBY70wSt/xKGehw6u0njrrvWmLVapReHVC/KBRNGyX3B2S4dvruSUbEzB5YCjFVB6DPswDwm94PTGG+cZQo2aG6aTBezFD2IwtyJoDKsXHJ2+0hllC8Ndotc2k87ei3ZIolRt47yftM8o6Tu+41MzZkL40ZRXRgPhuugCAhQ6dL/fGxzlk7cNhiKeIWYTih9lZyxpNNDS0vmee6T+jpP7OfGpzL0gjuQW1MeGAmDWI7vfc9Yb3K7n7kMDVR2a6SnyJkVqIHkjTB30Yqx0wcnNNv9q7Z7gyZ3LtGLwCl9twJXgitDE/AB3zLWj476yBXwkC4wSJUVpdeYpXcke4HTfj5By4GRz33EU1MgW+UQ6y2nhyvoIV4886HnJUELyZBkLtXEv3eGDiFIlHuHywdtYVqHxCDB1CzREbSpmRtPWpWs977rkvcIgczDu2Y2GZl7BCpJg1lDWR7g9L4Gxxhi34uArOp5q30rWVkEnh/gj3FFPxOnqs0B8aSYaJa8k2+6RSHGSh/c78hl0nW23GBmp4Xo6veVYaRN4hTzAyeZ7gpjp5hzorpAnEKGamrQs2coqFxzfbPdhF2RCtAUEXwNSi3gA3gcbzEwfPI/O52EobPgYMrXs7IQ5n4gk1Lq34HLhw95Xdj2RFydc1sGGtjwLejIwmqthecAsXebsZV8BXxKIg68nZ3HkbIPaYPAgn7rmvQ/l9ZztzPNFXBBDZs3wycimgv9gmUhIBfhBF5zd9yOKnLEJNZUlyOdZ1ZrB2XU29Xu+fo87+AA3WmDEoNHhP3kilC1VKoKoMPllrt/g0lkXjNdMnGdLSAbQQNgJFrgB6hYtevfNtzi6pSNfL5nEqYAWMD6MDRYcCDilYnALlo845AI7eNQgIqhKoGVu2BkLVBFeMzv8ZtbL89CHI/fMuXTUlEG7xH1iS8EtnRXxGozK/YLTU18q0sRCfhtjxyNcEPsY/vEEOkH7T52X/Hq/euZB5A6MxsEkj4pFNcHccfaCxERS5wWXz30lQ6BHHwksnHvUDAsLiL46+KVttQvupX/LZu4lpVoLsvFamO5gLK64LOUX3uZ3fTjqCe8YmOSWcC+YirJrQ5KVaYIhvTwPfRA75ozR94T5mlgDUpOFGGhYlToWeNd7Pe3tXB97XEeUQGxJWMMTvDYhywOMju50DQG44OzsX521otud2bHSUodYY8VmbJrWoW7X56Wb7XruESFYgSiWAKwqOJ3Jwv6HysYo1+elmx48yljgHf4KE0mrYtXldkhGGQ1Mng9wR95FoqM2phFcmseEeiIUJOzFiMnYYfQBjs7cYzwQjit1rB6gj08sLnU7Iizmnlvf4uCbxx6wJPe9Ow7QD0kXEhoZ60cocl7u/gB3+BGOhqCcEedcsPpB3OFmEF1oYK9o/AXH5/5XRyutdSQI7B8Q3dq0ChZJGDaSqVxxdOoSU19Y/rD5IR/Z5NR1QFuWQ1GxTVzOiTk7cpYxNu5i293364WRxXJFSEZShrvf8+dbXD3nOjp0xUFWYAjalxGSeZ8Vv8BRLn1AXjr6h4XfQD4aec7U8QdEyLiqUS+cx51H6TUv6c32XGOZaaVWCHbZr4c4dE62DA8WQyTkj7i9N+7ngUbQ3FomZqPHRGISKHCFQWE9SNYuuHrTI9etCV+dlGALcy+RndfoiF5l5/QuF5zeebRfpPhC9ZTZyPezkOoNGR854SWfpXfzcvrKxLKuuBWiZAMp2t78o2eMKszpUpftt3T4Q5I2sYTt9wUIJMh2SIhwGWgThrR/xNF9r9KUFr4+DdEdzg0rdUdKcNupt7zkQX3tA599GG2hE1DqChXSIggWGHIQCyzCXMcD3JGTkfxL6dNgS1jGKqJZONYqZOAVubN+xKF/h75wckHrOBBboYd47MR0Q6nBxYDPXHD11M/9ZqIhgKLmsyI2IdshmyFAGVwTgnXBldM359rJHaaEm80YLUygnojlKEpDbLrg7vvDSHNq3XF+LGQYDEsdXiBN8LJu9/chb3H3XDdScK+IrCvGgOdm8FbH9OTIBfHCP377vOO9DbZvOAnYjUC/8+deXgzVZERSdNYvODn5B8a1KClDkZCPwIYysB7HauiowIEvODr3fkMg5r0VYYoznEwCy+nYbxlghSjQiaN3fDh0AmyLIiPvF0UpRxtYtBTLPKStzvt7DW5vcYcujQIboMYZYjZguDvZecR+JVOwOZ715Hf+bnbs4Qi8BYkDa5RtZcBjsAAiwGofGNgL7s6/NLFMj4m8qUhXRZuGY+Vo8DCd6+5j/N4393x+ecYlTrfid9N0bM1/Cf/69A9/9p/i69PPz09/++fTjP/412+/xvO37w4UPvVH/355/hZf47dvMTf2y/dP33/1n58jPv/93/3p9qcvT/7r0/rlv/iq/fu/Pv/0y8+//evpz//3a8/d+f0OvGuuptBIBGJs6g3ZlNaS/ZKFkRpL2hnnx/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CreatedDate = _t, Ga_userid__c = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CreatedDate", type datetime}, {"Ga_userid__c", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type", each if Date.From([CreatedDate]) < #date(2020,2,20) then [Ga_userid__c] else null, null,Replacer.ReplaceValue,{"Ga_userid__c"})
in
#"Replaced Value"
Anonymous
6 years agoNot applicable
Hi ImkeF
Thank you for linking the article, it truly made me understand. I can continue now. The kudos are well deserved.
Regards,
Jdiep