Notifications
Clear all
Power Query | M code
2 Posts
2 Users
0 Reactions
2,120 Views
Topic starter 07/02/2020 12:29 am
Hi all,
I start this this topic for help on for the following 'problem'.
We daily receive Oracle csv files with time codes with miliseconds. This has 2 problems, I need to split this several times in a date and time column, and add 1 hour due to server time difference. Can it be done easy?
It is shown as this. 23-jan-20 10.01.37.946000 AM
https://cdn.filestackcontent.com/IzwRpR0UR2RZ5xMgNni Q" />
I only need a date column and a time column.
Tnx.
Nice website by the way 🙂
16/02/2020 4:07 pm
Hi Boudewijn, there are many solution. the best one which i can share with you
- Split Column by Delimiter with a space, that will separate date,time
- From the time column Extracted First 5 Characters
- Replace "." with ":" for time
- Combine column Time Column & AM/PM column
- Update the correct data types
hope the above step will resolve your query, you can also try the below code by pasting it in your query advance editor. Your Excel Table Name must be "Table1"
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Time.1", "Time.2", "Time.3"}),
#"Extracted First Characters" = Table.TransformColumns(#"Split Column by Delimiter", {{"Time.2", each Text.Start(_, 5), type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted First Characters",".",":",Replacer.ReplaceText,{"Time.2"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"Time.2", "Time.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Time"),
#"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Time.1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Time", type time}})
in
#"Changed Type"
