Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 text string (hash) of 32 characters (numbers and letters).
I have tried 7 different solutions I could find here, but none of them seem to work. It shouldn't be this hard, because I am guessing it's a Replace.Value and an IF statement. But what do I know, I am new to this.
Hope you guys can help me out. Thank you in advance.
Jdiep
PS: if you need example data let me know
Solved! Go to Solution.
Hi @Jdiep ,
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-p...
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
HI @Jdiep
yes, please post sample data. THanks.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Can't seem to upload a file here...
Hope this will work for you. Only after the 20th of februari is the data in GA_userid__c actually correct. The data has more rows that go all the way back to 2017, but I can't post the whole thing. So anyting before that date should null.
Thanks again!
CreatedDate | Ga_userid__c |
5-3-2020 08:48 | 285dffc130256e9e4d00567999ea3e10 |
5-3-2020 07:47 | 3edebc5972e74ae62f7326a27ec0574f |
5-3-2020 07:42 | 083eb946ae55dd355afc3fec689bb8e1 |
4-3-2020 21:02 | 1cf25da26f3a181f5a6bfde9c44704bf |
4-3-2020 18:08 | d5a0cf2810929ec675b70295fe714270 |
4-3-2020 15:35 | 81c953c662585d4b0cdcc32b7a60872f |
4-3-2020 15:26 | 191137aade3f4b766e919770526a9006 |
4-3-2020 14:17 | b6830fb9c01eab22add7721b59b26151 |
4-3-2020 14:13 | ca66c22f5e7164c42326cd850600343c |
4-3-2020 14:12 | 37cf90e76c4d18a80a2ebc54782285bf |
4-3-2020 14:11 | 1fa59a7cb372609e2b60e167cced9187 |
4-3-2020 14:10 | 1ea91c3f676512148a284ed1fc3e16bb |
4-3-2020 14:09 | 6b8f1ab553e9fe4e436ca332b21a8e3e |
4-3-2020 14:05 | 523437f4383452be52e9d1b6b380e94c |
4-3-2020 14:05 | df4ba583d7dc7b91e6e989701bb69daa |
4-3-2020 14:05 | e56f0d658e4daf1bac2f980338effc7f |
4-3-2020 13:29 | 1beb63b44e19c8a6e436b4cb7b2dec00 |
4-3-2020 12:13 | bdd91f5c59142e6c86423a0f0f12f476 |
4-3-2020 11:24 | 1be986cfafb8f98312d2d830aeab044e |
4-3-2020 11:22 | 831d0b4ff9749067ca3afa95a418434b |
4-3-2020 11:19 | 7ac420700ea18b7b24bd8dc3d36ed65f |
3-3-2020 19:21 | 49afabce5aa35f5123025e44e2255411 |
3-3-2020 19:00 | 72b41398fc25da89f66167e92cd00175 |
3-3-2020 16:36 | 0ce259ee4fbfb4bd9df01e03e61a87cf |
3-3-2020 15:33 | 03844c4d823e5c5155104aac2e5651a8 |
3-3-2020 14:07 | b0e9ca77a7c07934e98c02d218ea8f20 |
3-3-2020 13:40 | e0a6351d55778e5d5409ad22032aceee |
3-3-2020 10:24 | a085c288630dd6828ef200be7b8dbc2e |
3-3-2020 09:42 | e342254d1e89c7f52c1031cab356a571 |
2-3-2020 23:48 | 23381060876ad8e08e1872bf7d0865d8 |
2-3-2020 22:34 | bf1c6ac15e4c1e94c88438a5a53c94c7 |
2-3-2020 18:47 | e6d7afd3d7c71b35d212df6d0f60f0b6 |
2-3-2020 16:45 | 71ae03c41f086e208f6f012289907851 |
2-3-2020 14:19 | 273ed3ed6191d83f2bb16700b4b06019 |
2-3-2020 12:42 | 674795ac1d0ca2340d76a15df68e353d |
2-3-2020 12:33 | a0a0ec102c0906743f369081f3ba22c2 |
2-3-2020 10:21 | 5f5cc051177981829b7703f1ddd5bd8e |
1-3-2020 20:17 | acaeb0377c384ea9936a9fff59ebc1f6 |
1-3-2020 16:05 | f79137f45b939ef7873f5af83199e96a |
1-3-2020 15:46 | 1f4a045cd2674ef70fe36c0a6c5ae2e3 |
1-3-2020 13:16 | a67dc4b059c7124fb124011e0883ec58 |
1-3-2020 10:09 | b1a22e5bc1eb89d43dbb73bf982839c6 |
29-2-2020 15:33 | bb7ded4deb346f634064c1f31300a06a |
29-2-2020 06:40 | 9d7d2a6a35dbebe75679290cc107b1e1 |
28-2-2020 20:09 | 79261b781c69e0bcbe757b155be6aa4f |
28-2-2020 15:45 | 9f52a302a4e63d7c49c2e90d0c09acf4 |
28-2-2020 12:54 | 88ba4c3eb17d575986af9ce43ba59df6 |
28-2-2020 12:42 | a45baf65eeab62daad431eb3d6d2b305 |
28-2-2020 12:39 | 0772d91d1b78c77d25f0adfe764fc693 |
28-2-2020 12:38 | 1ca6f2c3062f12cfe0baa7ba864b687e |
28-2-2020 12:36 | 27ef58bfbb37851a8c7fd324034865df |
28-2-2020 12:34 | ab8ab06e145bb50c361fe13aeb1e00d0 |
28-2-2020 12:33 | e4a0c0a97a36992948b53c99f7be7c2a |
28-2-2020 12:32 | a7328ad72250d40c76f425c596628abe |
28-2-2020 12:31 | 1ef2159acf32b9a8c3da96ef4936afe2 |
28-2-2020 12:27 | 4d3e3fe363843ac1f9885352962657f7 |
28-2-2020 11:58 | e145dbf12319fc0f07499efe152d23e2 |
28-2-2020 10:27 | 2b35cacc2d37e56f792dfb809ba9e171 |
28-2-2020 08:43 | fc3d515a69e9fa62b06cba5f9b8e0775 |
28-2-2020 07:47 | 5be457b9ba0a12a9c0545c91dba23fda |
27-2-2020 19:26 | 72a2bced868dbe6532ced1094a8794a0 |
27-2-2020 15:47 | 30e66057932f222f6335958f23d93abf |
27-2-2020 15:39 | ae0f72dc0cb0786db04f3307277ad17e |
27-2-2020 14:43 | a36d416d9455712468eea3c9ce7373c7 |
27-2-2020 10:49 | 6f2d01477ef6f0acf7a6137047518212 |
27-2-2020 09:56 | 29cf4d86e809a50057ed4f5cdb5ccd87 |
27-2-2020 07:07 | ddf3c846c7f41bd600786a13bb661f6b |
27-2-2020 03:42 | 8810f175cca8f2124cdc212a4b5359c8 |
26-2-2020 18:00 | 8e19b88ad72ae8ae641229cf7203028d |
26-2-2020 16:10 | 6567cbc4328d9e9f58a2e00fbef239f9 |
26-2-2020 15:39 | af5d6b744887a41ff7e5e1da21bd06be |
26-2-2020 10:46 | 206accba94dc240631a394132d521db9 |
26-2-2020 09:25 | 301d414f63d2e6492face59feb9eaa3b |
25-2-2020 19:11 | b10283f4e5540fe4fa94924d855413ac |
25-2-2020 15:07 | 8d88005b11a5260b0add5a143c5b294b |
25-2-2020 14:50 | 50050d2b23b7b4239ea52fd1ff58ec7c |
25-2-2020 14:37 | 365a0af31b304f8c2219d37e57c90a1d |
25-2-2020 14:24 | c4109cd5046f1cd6f411e48502acf973 |
25-2-2020 14:07 | ac51c8ef80b5aa9ceada188c3ebe0bd8 |
25-2-2020 12:53 | 0f26bf4bac571872ac1e689fe30ca6ba |
25-2-2020 12:36 | cd638163f3a243662812c962e17b81db |
25-2-2020 11:13 | 8c9799b90c8440748938263b98673e2b |
25-2-2020 10:47 | 10bffaf98383e5d14b3c277faa695d5a |
25-2-2020 09:16 | 51b2175eab6cd85c62568899228305c2 |
25-2-2020 08:46 | ebf6facdc85c03d9667019382ebf0bf3 |
25-2-2020 06:24 | 55354320c6dd4bbbb2d5684450b716ce |
24-2-2020 23:51 | dbc9788ed87fde91e3dd1560c0258831 |
24-2-2020 19:04 | 01dc0c87d973bedd7c2cb08cc2e03459 |
24-2-2020 18:30 | 9fd6afd04cedd0ce2b1fcb2a370906b2 |
24-2-2020 13:07 | 81fa5af28331150ab08c7435ec1699f4 |
24-2-2020 12:37 | 30d30a33dda359b3098642eb69ce532e |
24-2-2020 10:20 | a3429de189142c0a575f520cb1dafe3b |
24-2-2020 08:47 | 344f2eb4c235cd25a89be36a54c1f7f6 |
23-2-2020 21:51 | c9fbc9f218aa337236363f8efe0b81de |
23-2-2020 21:40 | f7077bd5f2d2088d32ea70061bfe6b13 |
23-2-2020 19:50 | 14a29731e7d541418ad5d139f872ebd6 |
23-2-2020 18:25 | 700e9d9199fd89acb509f759a573c0d2 |
23-2-2020 17:49 | dfc107810236dec9e5224d55bf7f9529 |
23-2-2020 15:33 | c4dd3881c6cf69e1fc8ca71e412ab55f |
23-2-2020 12:31 | c4e1b8785fecc8096b3c3cda4a7adec2 |
21-2-2020 15:22 | 6ad1a3aa6b179929ded4561b1a974f2a |
21-2-2020 12:24 | bbb9e74666223f7c27c6e2ef921828f2 |
21-2-2020 10:48 | 5eb1134066996c242e2c2c7b8dff74d5 |
20-2-2020 13:06 | ecb0e72c60ea146e9cb7d37c77488dee |
19-2-2020 13:16 | c790a0916e5dca41a9c0aee4c4d75aef |
16-2-2020 16:55 | 67d3e71f6e6a55883488b730f03bc337 |
16-2-2020 12:37 | 4d051cdf9b33c97393ea64b9ec73df7c |
15-2-2020 15:00 | Indeed |
13-2-2020 17:05 | Dear Manager in HR department, Interested in IT Trainee/Job :(I am fluent in English ) I |
9-2-2020 15:36 | 3533be68d74e93070ff20f0b104774be |
Hi @Jdiep ,
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-p...
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF
Thank you for linking the article, it truly made me understand. I can continue now. The kudos are well deserved.
Regards,
Jdiep