Error when aggregating dates in a power query

Occasional Contributor



I am using a power query to group and aggregate columns of a table (with great success so far thanks to members of this forum!). The latest obstacle is dates - one of the columns features dates and the power query returns an error when I try to aggregate these. Below are screenshots of the grouping/aggregating logic, the M code, and the error that returns on the date column. How can I resolve this error?


Grouping/aggregating logic



M code






9 Replies

Hi @102938 


I'm not quite clear on how the Group By settings (your 1st pic.) where the aggregation (Operation) is Sum for every fields can generate the M code in the 2nd pic. (???). Anyway...


#1 - Is your query going to do something else than these aggregations, more specifically with field [Start]?

#2 - Could you confirm that in your Changed Type step you have {"Start", type datetime}?


@L z. thanks for your reply!


I manually edited the M code after the grouping to include text.combine and list.distinct as these give me the desired result for my other columns.


#1 - no the query is not going to do anything else after aggregating.


#2 - no I did not have {"Start", type datetime} in my changed type step. I have now changed it to this, but it seems I have the same error.


New M code in changed type  step



Error on date column




Re. #2 I didn't suggest that you changed Start to datetime


Before your Changed Type step, how do your your [Start] values look like: mm/dd/yyyy or mm/dd/yyyy hh:mm:ss?

@L z. Before the Changed Type step, the dates are mm/dd/yyyy hh:mm:ss. Screenshot below of how they are coming through from the source table.



best response confirmed by 102938 (Occasional Contributor)



OK, so:

#1 Edit your existing Changed Type step to set {"Start", type date}

#2 In the APPLIED STEPS, select step Changed Type

#3 Select column [Start] > Home (tab) > Data Type: Text

     3a) You're asked to Insert Step > Confirm

     3b) Then you're asked Replace current or Add new step  > Add new step

#4 With the Advanced Editor edit your Grouped Rows step and at the end where you currently have:

          {"Start", each Text.Combine(List.Distinct([Start]), ", "), type nullable date}

replace the date with text


Example with only 2 aggregations:


@L z.  Thankyou! This worked perfectly!


As a follow up question, is there a way to only show the earliest date for each row? Currently the aggregation shows all distinct dates (screenshot below).




Don't get me wrong please but next time ask for the actual expected result from the beginning as we need to revert things now:


#1 Keep your Change Type step with {"Start", type date}

#2 In the APPLIED STEPS delete the 2nd Changed Type I asked you to create, the one that changes again Start to type text

#3 With the Advanced Editor, revise your aggregration that currently says:

     {"Start", each Text.Combine(List.Distinct([Start]), ", "), type nullable date}


     {"Start", each List.Min([Start]), type nullable date}



@L z. Thankyou, again this worked perfectly!

@102938. You're welcome. Glad I could help