Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jdiep
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
ImkeF
Super User
Super User

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors