Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

paivers

Nvarchar from view not grouping via dataflow

Submitted by on ‎02-20-2023 06:18 AM

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.

paivers_0-1676901410409.png

 

Status: Investigating

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 _ Caitlyn

See more ideas labeled with:
3 Comments (3 New)
Comments
v-xiaoyan-msft
Community Support
Community Support
‎02-20-2023 09:52 PM
Status changed to: Investigating

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 _ Caitlyn

paivers
Regular Visitor
‎02-21-2023 06:14 AM

Hi @v-xiaoyan-msft , it is import mode of a dataflow, desktop version : 2.110.1161.0 64-bit (October 2022)

paivers
Regular Visitor
‎02-21-2023 07:55 AM

@v-xiaoyan-msft , 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.

Idea Statuses