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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas for Year, Quarter, Month name and Month number.  Sort the Month name column by the Month number.  To your visual, drag Year/Quarter/Month name from the Calendar Table.

  • jaytrice26's avatar
    jaytrice26
    Helper I
    2 years ago

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

    • 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?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      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.