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:
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:
Lorenzo 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}
with
{"Start", each List.Min([Start]), type nullable date}