Aug 03 2022 02:25 AM
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
Aug 03 2022 02:52 AM
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}?
Aug 03 2022 03:01 AM
@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
Aug 03 2022 03:07 AM
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?
Aug 03 2022 03:14 AM
@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.
Aug 03 2022 03:29 AM
Solution
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:
Aug 03 2022 03:42 AM
@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).
Aug 03 2022 04:03 AM
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}
Aug 03 2022 04:51 AM
@L z. Thankyou, again this worked perfectly!
Aug 03 2022 05:53 AM
Aug 03 2022 03:29 AM
Solution
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: