trying to get GroupBy to include a calculated column?

[BarryB]

Hi

I've been able to get Counts using GroupBy in PowerQuery

And I've been able to convert an ISO8601 Timestamp field into a new Column as a Year+Month field to aggregate the data

YearMonth = FORMAT([TimestampField], "MMMYYYY" )

.... but not together: 

"Column 'TimestampField' cannot be found or may not be used in this expression." when trying to run the GroupBy (and, yes, the "YearMonth" calculated field is not available in the GroupBy clause).

inputs:

HostsName | TimestampField

=================

Outputs (target/aim): to be used for a stacked bar graph:

 - X-Axis: per month over a multi-year period

 - Y-Axis: counts of each "host"

HostsName | YearMonth | HostsCount

=========================

I think this is because the New (calculated) Column ("YearMonth") cannot be included in the GroupBy clause because it only exists in memory/at runtime.... BUT the YearMonth is needed to give meaning to the Grouping of the Hosts to produce the counts of each (per month).

Is my only option to try and turn the Calculated Column into a physical column (how)? AND THEN do the GroupBy?

Sorry for the n00B questions but I can't find examples of this scenario.

Thanks in anticipation

Barry

[v-yuta-msft]

Hi BarryB ,

Based on your description, you have created a calculated column using DAX in report level, right? In power query, all the contents in report level can't be seen and used. 

So if you want to use group by in power query, you should create a custom column in power query, click Add Column-> Custom Function, then use M code below:

Custom Column = Date.ToText([TimestampField], "MMMyyyy")

  

Then you can use group by like below:

 

Regards,

Jimmy Tao

[BarryB]

Uh oh!

Type conversion error:

Expression.Error: We cannot convert the value #datetimezone(2018, 5, 8, 10, 40, 18, 10, 0) to type Date.
Details:
Value=8/05/2018 10:40:18 AM +10:00
Type=Type

The "TimeStampField" is actually an ISO8601 date+time+timezone format.

Now, I could try to find a workaround ... BUT (until I can get this working and prove it otherwise)

... I am concerned that "Date.ToText" will not allow me to sort by date for the stacked bar graph's X-axis

... because it is text. 

[v-yuta-msft · Accepted solution]

Hi BarryB ,

Use this and try again

Custom Column = Date.FromText(Date.ToText([TimestampField], "MMMyyyy"))

Regards,

Jimmy Tao

[BarryB]

Thanks Jimmy.

I'm going to mark your answer as a solution 

HOWEVER ... I think the core issue is that I think I've hit a limitation with the M-Query functions - a bug.

I don't think the "Date.ToText" function can work with ISO8601 timestamps (date+time+timezone).

Expression.Error: We cannot convert the value #datetimezone(2016, 1, 3, 8, 15, 6, 10, 0) to type Date.
Details:
Value=3/01/2016 8:15:06 AM +10:00
Type=Type

I'm actually thinking it's a bug in the funtion

Why? Because the inbuilt PowerQueryEditor can transform the ISO8601 Timestamp to a date on the column (right-click on the column name then "Transform - Date Only") without throwing errors.

... and THEN the 

Date.FromText(Date.ToText([TimestampField], "MMMyyyy"))

works to create the custom column... which is a work-around to get past the limitation of the M-Query function of Date.ToText()

But yes, once I do that, I can get my three column aggrigation of "Start of the MonthYear", "Field", CountOfField.

... which is what I was aiming for.

Thanks again for your help. Mystery Managed.

Barry

[BarryB]

OK, so I'm now trying to turn the Calculated column into a more permanant one so it can be part of the Group-By clause and therefore overcoming this issue.

it'd didn't quite work:

Expression.Error: The name 'FORMAT' wasn't recognized.  Make sure it's spelled correctly.

it looks like I need a "Power Query M Function" of which "FORMAT" isn't...

but even then, I still don't think that's going to be a solution, if the "YEARMONTH" column becomes converted to a text field instead of remaining a Date field, because the X-Axis needs to be in date order (so "Apr2017" still comes after "Mar2017")

My aim is to try and do as much data shaping as possible within the Power Query Editor, not pre-processing through upstream systems.