SOLVED

Error when aggregating dates in a power query

Occasional Contributor

Hello,

 

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

1.JPG

 

M code

3.JPG

 

Error

2.JPG

 

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

4.JPG

 

Error on date column

5.JPG

@102938 

 

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.

 

6.JPG

best response confirmed by 102938 (Occasional Contributor)
Solution

@102938 

 

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:

_Screenshot.png

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

 

7.JPG

@102938 

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}

with

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

 

_Screenshot.png

@L z. Thankyou, again this worked perfectly!

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