We are seeing a behaviour where a field defined in a SQLServer view will not group correctly when read into a dataset via a dataflow. The example below is a date dimension. MonYY is showing one row per day in the month, even though day is not in the visual. Normal behaviour is like YearMonth grouping to a single row per month, that field is also an nvarchar but defined in the base table. This only happens when MonYY is accessed via a dataflow. If you do a get data to the view directly it works correctly. The formats of the fields at the top of the screenshot are identical. Thinking there was some hidden data in MonYY we tried Trim(MonYY) which seemed to fix it, however checking the Len() of the field, it is 6, so nothing hidden. Any ideas why this odd behaviour.
3 Comments
Hi paivers ,
It would be great if you could provide more information.
1. Your version of Power BI Desktop
2. What connection mode are you using to connect to your data source? Is the problem occurring in import mode or directquery mode?Best Regards,
Community Support Team _ CaitlynHi Anonymous , it is import mode of a dataflow, desktop version : 2.110.1161.0 64-bit (October 2022)
Anonymous , I think I found where the issue came in, and so was able to resolve, but this might be a bug in how dataflows are handled. This was an existing dataflow imported into a dataset. The dataflow query is a select * from a datedim SQLview, we use that as by definition we want to bring in all view fields. MonYY was added to the view, but does not show up in the dataflow by default, you edit the query, save and refesh and it was brought in. You then have to edit your dataset query of the dataflow, and it brings in the MonYY, but although it is imported as a Text field it has this odd non grouping behaviour. The solution was to delete the table and bring it in from a new Get Data and reapply any relationships and formatting. I think this is probably reproducable.
