Forum Discussion
Grouping Data
Hi,
In the Query Editor, seperate that column into 2 - one with Date and another with time. Now build the Calender Table.
Ashish_Mathur Thank you for the feedback. I've followed your steps of trying to create a calendar table but when entering my formula to create the data I'm getting an error" Cannot convert value '2023-11-13 (11:56PM)' of type Text to type Date." Any ideas? I did try to transform my data from Text to Date but it just produces another error, "DataFormat.Error: We couldn't parse the input provided as a Date value. Details: 2022-10-19 (12:21 AM).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY1NNQ1NFbQMDS0MjUL8NVUitUBSxjpGhroGloCJYysjAwVHEEysQA=", 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}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type date}})
in
#"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
lbendlin - thank you for the quick response but can you explain this to me? I did follow your instructions and create the new query and once I was finished it did display 2 dates. How am I supposed to use this to fix my issue?
Use my code as a guidance (split by delimiter, throw away right part).
Replace the Source= line with your own source. Adjust column names as needed.