Forum Discussion

ColinCowan's avatar
ColinCowan
Helper I
3 years ago
Solved

Replace Numbers with Date Sequencial

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

 

Need Help
  • ColinCowan's avatar
    ColinCowan
    3 years ago

    Hi, thanks that works and easier much appreciated.

     

    Regards Colin

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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

12 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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"
  • ColinCowan's avatar
    ColinCowan
    Helper I
    3 years ago

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

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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