cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

1 ACCEPTED 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

View solution in original post

4 REPLIES 4
Super User II
Super User II

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!

CreatedDateGa_userid__c
5-3-2020 08:48285dffc130256e9e4d00567999ea3e10
5-3-2020 07:473edebc5972e74ae62f7326a27ec0574f
5-3-2020 07:42083eb946ae55dd355afc3fec689bb8e1
4-3-2020 21:021cf25da26f3a181f5a6bfde9c44704bf
4-3-2020 18:08d5a0cf2810929ec675b70295fe714270
4-3-2020 15:3581c953c662585d4b0cdcc32b7a60872f
4-3-2020 15:26191137aade3f4b766e919770526a9006
4-3-2020 14:17b6830fb9c01eab22add7721b59b26151
4-3-2020 14:13ca66c22f5e7164c42326cd850600343c
4-3-2020 14:1237cf90e76c4d18a80a2ebc54782285bf
4-3-2020 14:111fa59a7cb372609e2b60e167cced9187
4-3-2020 14:101ea91c3f676512148a284ed1fc3e16bb
4-3-2020 14:096b8f1ab553e9fe4e436ca332b21a8e3e
4-3-2020 14:05523437f4383452be52e9d1b6b380e94c
4-3-2020 14:05df4ba583d7dc7b91e6e989701bb69daa
4-3-2020 14:05e56f0d658e4daf1bac2f980338effc7f
4-3-2020 13:291beb63b44e19c8a6e436b4cb7b2dec00
4-3-2020 12:13bdd91f5c59142e6c86423a0f0f12f476
4-3-2020 11:241be986cfafb8f98312d2d830aeab044e
4-3-2020 11:22831d0b4ff9749067ca3afa95a418434b
4-3-2020 11:197ac420700ea18b7b24bd8dc3d36ed65f
3-3-2020 19:2149afabce5aa35f5123025e44e2255411
3-3-2020 19:0072b41398fc25da89f66167e92cd00175
3-3-2020 16:360ce259ee4fbfb4bd9df01e03e61a87cf
3-3-2020 15:3303844c4d823e5c5155104aac2e5651a8
3-3-2020 14:07b0e9ca77a7c07934e98c02d218ea8f20
3-3-2020 13:40e0a6351d55778e5d5409ad22032aceee
3-3-2020 10:24a085c288630dd6828ef200be7b8dbc2e
3-3-2020 09:42e342254d1e89c7f52c1031cab356a571
2-3-2020 23:4823381060876ad8e08e1872bf7d0865d8
2-3-2020 22:34bf1c6ac15e4c1e94c88438a5a53c94c7
2-3-2020 18:47e6d7afd3d7c71b35d212df6d0f60f0b6
2-3-2020 16:4571ae03c41f086e208f6f012289907851
2-3-2020 14:19273ed3ed6191d83f2bb16700b4b06019
2-3-2020 12:42674795ac1d0ca2340d76a15df68e353d
2-3-2020 12:33a0a0ec102c0906743f369081f3ba22c2
2-3-2020 10:215f5cc051177981829b7703f1ddd5bd8e
1-3-2020 20:17acaeb0377c384ea9936a9fff59ebc1f6
1-3-2020 16:05f79137f45b939ef7873f5af83199e96a
1-3-2020 15:461f4a045cd2674ef70fe36c0a6c5ae2e3
1-3-2020 13:16a67dc4b059c7124fb124011e0883ec58
1-3-2020 10:09b1a22e5bc1eb89d43dbb73bf982839c6
29-2-2020 15:33bb7ded4deb346f634064c1f31300a06a
29-2-2020 06:409d7d2a6a35dbebe75679290cc107b1e1
28-2-2020 20:0979261b781c69e0bcbe757b155be6aa4f
28-2-2020 15:459f52a302a4e63d7c49c2e90d0c09acf4
28-2-2020 12:5488ba4c3eb17d575986af9ce43ba59df6
28-2-2020 12:42a45baf65eeab62daad431eb3d6d2b305
28-2-2020 12:390772d91d1b78c77d25f0adfe764fc693
28-2-2020 12:381ca6f2c3062f12cfe0baa7ba864b687e
28-2-2020 12:3627ef58bfbb37851a8c7fd324034865df
28-2-2020 12:34ab8ab06e145bb50c361fe13aeb1e00d0
28-2-2020 12:33e4a0c0a97a36992948b53c99f7be7c2a
28-2-2020 12:32a7328ad72250d40c76f425c596628abe
28-2-2020 12:311ef2159acf32b9a8c3da96ef4936afe2
28-2-2020 12:274d3e3fe363843ac1f9885352962657f7
28-2-2020 11:58e145dbf12319fc0f07499efe152d23e2
28-2-2020 10:272b35cacc2d37e56f792dfb809ba9e171
28-2-2020 08:43fc3d515a69e9fa62b06cba5f9b8e0775
28-2-2020 07:475be457b9ba0a12a9c0545c91dba23fda
27-2-2020 19:2672a2bced868dbe6532ced1094a8794a0
27-2-2020 15:4730e66057932f222f6335958f23d93abf
27-2-2020 15:39ae0f72dc0cb0786db04f3307277ad17e
27-2-2020 14:43a36d416d9455712468eea3c9ce7373c7
27-2-2020 10:496f2d01477ef6f0acf7a6137047518212
27-2-2020 09:5629cf4d86e809a50057ed4f5cdb5ccd87
27-2-2020 07:07ddf3c846c7f41bd600786a13bb661f6b
27-2-2020 03:428810f175cca8f2124cdc212a4b5359c8
26-2-2020 18:008e19b88ad72ae8ae641229cf7203028d
26-2-2020 16:106567cbc4328d9e9f58a2e00fbef239f9
26-2-2020 15:39af5d6b744887a41ff7e5e1da21bd06be
26-2-2020 10:46206accba94dc240631a394132d521db9
26-2-2020 09:25301d414f63d2e6492face59feb9eaa3b
25-2-2020 19:11b10283f4e5540fe4fa94924d855413ac
25-2-2020 15:078d88005b11a5260b0add5a143c5b294b
25-2-2020 14:5050050d2b23b7b4239ea52fd1ff58ec7c
25-2-2020 14:37365a0af31b304f8c2219d37e57c90a1d
25-2-2020 14:24c4109cd5046f1cd6f411e48502acf973
25-2-2020 14:07ac51c8ef80b5aa9ceada188c3ebe0bd8
25-2-2020 12:530f26bf4bac571872ac1e689fe30ca6ba
25-2-2020 12:36cd638163f3a243662812c962e17b81db
25-2-2020 11:138c9799b90c8440748938263b98673e2b
25-2-2020 10:4710bffaf98383e5d14b3c277faa695d5a
25-2-2020 09:1651b2175eab6cd85c62568899228305c2
25-2-2020 08:46ebf6facdc85c03d9667019382ebf0bf3
25-2-2020 06:2455354320c6dd4bbbb2d5684450b716ce
24-2-2020 23:51dbc9788ed87fde91e3dd1560c0258831
24-2-2020 19:0401dc0c87d973bedd7c2cb08cc2e03459
24-2-2020 18:309fd6afd04cedd0ce2b1fcb2a370906b2
24-2-2020 13:0781fa5af28331150ab08c7435ec1699f4
24-2-2020 12:3730d30a33dda359b3098642eb69ce532e
24-2-2020 10:20a3429de189142c0a575f520cb1dafe3b
24-2-2020 08:47344f2eb4c235cd25a89be36a54c1f7f6
23-2-2020 21:51c9fbc9f218aa337236363f8efe0b81de
23-2-2020 21:40f7077bd5f2d2088d32ea70061bfe6b13
23-2-2020 19:5014a29731e7d541418ad5d139f872ebd6
23-2-2020 18:25700e9d9199fd89acb509f759a573c0d2
23-2-2020 17:49dfc107810236dec9e5224d55bf7f9529
23-2-2020 15:33c4dd3881c6cf69e1fc8ca71e412ab55f
23-2-2020 12:31c4e1b8785fecc8096b3c3cda4a7adec2
21-2-2020 15:226ad1a3aa6b179929ded4561b1a974f2a
21-2-2020 12:24bbb9e74666223f7c27c6e2ef921828f2
21-2-2020 10:485eb1134066996c242e2c2c7b8dff74d5
20-2-2020 13:06ecb0e72c60ea146e9cb7d37c77488dee
19-2-2020 13:16c790a0916e5dca41a9c0aee4c4d75aef
16-2-2020 16:5567d3e71f6e6a55883488b730f03bc337
16-2-2020 12:374d051cdf9b33c97393ea64b9ec73df7c
15-2-2020 15:00Indeed
13-2-2020 17:05Dear Manager in HR department,



Interested in IT Trainee/Job :(I am fluent in English )



I
9-2-2020 15:363533be68d74e93070ff20f0b104774be

 

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

View solution in original post

Hi @ImkeF 

 

Thank you for linking the article, it truly made me understand. I can continue now. The kudos are well deserved. 

 

Regards,

Jdiep

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors