Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Date for all rows

Hello,   I have a folder with multiple files, everyday there comes a new file in the folder. (it is a summary with all the production numbers each day).   Each file contains the date of that day ...
Need Help
ppm1's avatar
ppm1
Icon for Solution Sage rankSolution Sage
3 years ago

Does your filename include the date? If so, it might just be easier to parse it out of that and convert to a date.  If not, you may be able to use this shift and fill technique to get the date on every row.

https://www.youtube.com/watch?v=VEjsze31MPU

 

Also, if your refresh slows down, it is also possible to leverage incremental refresh in your published dataset (so you are not reprocessing every file ever time).

https://www.youtube.com/watch?v=IVMdg16yBKE

 

Pat

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

My file name includes the date, but how do i parse it out and convert it to a date?

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi Anonymous,

     

    Does it have a fixed position in the file name?

    And/or can you share a representative example of these file names?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

       

      It looks like this. The data is very messy, i have to clean it up. But this is for the example

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

       

      It looks like this. The data is very messy, i have to clean it up. But this is for the example

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I fixed it, thanks for the help!

      • m_dekorte's avatar
        m_dekorte
        Icon for Resident Rockstar rankResident Rockstar
        3 years ago

        Hi Anonymous 

         

        Give something like this a go

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLslMLS5JLMksBjKyU/MU8hITixRSK5JTcxSMDIyMdQ1MdQ1MdA3MdQ0MdI0M9SpyiiuUYnVI0W1KkW4zdN2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    AddDate = Table.AddColumn(Source, "Date", each 
        [ 
            v = Splitter.SplitTextByCharacterTransition( {"A".."z", " "}, {"0".."9"})([Name]){1}?, 
            r = try Date.From( Text.Start(v, 10), "nl-NL") otherwise null
        ][r], type date 
    )
in
    AddDate

         

        Ps. If this helps solve your query please mark this post as Solution, thanks!