Forum Discussion
Error when aggregating dates in a power query
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:
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}?
Lorenzo 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?
Lorenzo 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.
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: