Replace Numbers with Date Sequencial

[ColinCowan]

Hi, hoping someone can help my aim is to replace the numbers with the specified date after each date change.

[ColinCowan · Accepted solution]

Hi, thanks that works and easier much appreciated.

Regards Colin

[ColinCowan]

Greg, I adde a custom column copy pasted the formula and ended up with this. Please rememer I am a complete novice.

[Greg_Deckler]

@ColinCowan I'll walk you through it.

1. Get rid of that column you just added.
2. Add Column tab in the ribbon, Custom Column
3. Paste this into the formula area: try DateTime.FromText([Column1 - Copy]) otherwise null
4. Click OK
5. Right-click the header of your new column and choose Fill and then Down
6. Remove your original column (Column1 - Copy) by right-clicking the column header and choosing Remove
7. Rename your new column if you so desire

[ColinCowan]

Greg

See result

[Greg_Deckler]

@ColinCowan Apparently there are extra spaces or something in there. Delete [Column1 - Copy] in your formula and leave your cursor at right after the (. Double-click your column in the Available columns section. Click OK.

[ColinCowan]

Greg, I tried this also

[ColinCowan]

Greg

Error code

[Greg_Deckler]

@ColinCowan No, that's not the formula I posted, the formula for your added column is:

try DateTime.FromText([Column1 - Copy]) otherwise null

Your added column formula is literally not that. At all. Your formula doesn't include the try statement or the otherwise. See PBIX attached below signature.

[ColinCowan]

Greg

Thanks Can I reset as I am now confussed should the formula be

[v-yanjiang-msft]

Hi @ColinCowan ,

Another way maybe easy to understand for you.

1.Select the column then click Data Type option.

2.Select Date/Time.

3.Then you will find the numbers return error, right-click the column and select Replace Errors.

4.Enter null in the dialog.

5.Select the column, then click Fill Down.

Result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ColinCowan · Accepted solution]

Hi, thanks that works and easier much appreciated.

Regards Colin

[v-yanjiang-msft · Accepted solution]

Hi @ColinCowan ,

My pleasure!

If it works, would you mind accept it as solution? Then more people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[Greg_Deckler]

@ColinCowan You can do this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzbUN7SwtFQwsjI1tTIwUAjwVYrVAcqASSMwaQ4mjcGkCZg0BZMWYNIMiUQyz9jKGNk8iA5DUySjDCF6LGHmxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Column2", each try DateTime.FromText([Column1]) otherwise null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2", "Column1"}})
in
    #"Renamed Columns"