Grouping Data

[jaytrice26]

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 quarter. When I created the groups in my data view it allows me to choose the data I want to group and creates it with only those dates but when I look at the data for that new group it is still showing all dates for 2022-2023.

[Ashish_Mathur]

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]

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

[Ashish_Mathur · Accepted solution]

Hi,

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

[jaytrice26]

That totally worked and I was able to create my calendar table and customize with the help of this video from Haven's Consulting https://www.youtube.com/watch?v=Cxdk_atKFBM

[lbendlin]

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]

@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]

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.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...