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