Forum Discussion

jaytrice26's avatar
jaytrice26
Helper I
2 years ago
Solved

Grouping Data

Hello! I'm trying to group my order dates into quarters. Currently it is showing each individual date an order was placed in this format 2023-10-19. I want to be able to group them by month and quar...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    In the Query Editor, seperate that column into 2 - one with Date and another with time.  Now build the Calender Table.

lbendlin's avatar
lbendlin
Super User
2 years ago

 

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".

 

jaytrice26's avatar
jaytrice26
Helper I
2 years ago

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?

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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.