Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi BarryB ,
Use this and try again
Custom Column = Date.FromText(Date.ToText([TimestampField], "MMMyyyy"))
Regards,
Jimmy Tao
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
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.
Hi BarryB ,
Use this and try again
Custom Column = Date.FromText(Date.ToText([TimestampField], "MMMyyyy"))
Regards,
Jimmy Tao
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
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.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |