Amy has attended:
Power BI Reporting course
Creating an event dashboard associated with one event
I'm attempting to build a dedicated dashboard to show event registrations for one event that is hosted in Dynamics.
When I try to transform the data in Power Query from the msevtmgt_event table to then isolate the event, I get the following error - Preview.Error: The type of the current preview value is too complex to display.
I've tried to isolate the event in the Table view, but this doesn't reflect the data that is pulled through.
RE: Creating an event dashboard associated with one event
Hi
It sounds like you're encountering a common issue in Power Query when dealing with complex data types. The error message "Preview.Error: The type of the current preview value is too complex to display" typically occurs when Power Query encounters a data type it cannot easily render in the preview pane. This can happen with nested tables, records, or other complex data structures.
Steps to Resolve the Issue:
Expand Columns: In Power Query, try expanding the columns that contain complex data types. This can be done by clicking on the expand icon (usually two arrows pointing away from each other) next to the column header. This will allow you to see the underlying data and work with it more easily.
Convert Data Types: Ensure that the data types are correctly set. Sometimes, converting columns to a simpler data type (e.g., text or number) can help. You can do this by selecting the column, going to the "Transform" tab, and choosing the appropriate data type.
Filter Rows: Apply filters to isolate the specific event. This can be done by adding a filter step in Power Query to only include rows where the event ID matches the desired event. This will help you focus on the relevant data and avoid the complexity of other events.
Example Steps:
Expand Columns:
In the Power Query Editor, find the column with the complex data type.
Click on the expand icon and select the fields you want to include in your table.
Convert Data Types:
Select the column with the complex data type.
Go to the "Transform" tab and choose the appropriate data type (e.g., text, number).
Filter Rows:
Add a filter step by clicking on the drop-down arrow in the column header.
Select "Text Filters" or "Number Filters" and specify the criteria to isolate the event.
Try these steps and then let us know if that's helped
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Creating an event dashboard associated with one event
Thanks, Richard.
I've tried this, but the data table doesn't load in, and just shows the same error. Would I be better downloading the data from Dynamics and using Excel as the data source?
RE: Creating an event dashboard associated with one event
Hi Amy
Yes you are correct a work around in Excel by exporting the data from Dynamics to Excel and then using Excel as your data source in Power BI. This can sometimes help bypass the complexity issues in Power Query when we don't know what's stopping it working
Three other things to try would be
Disable Relationships: Sometimes, the relationships between tables can cause issues. Try disabling the creation of navigation properties in the advanced editor. You can do this by modifying your source step to include [CreateNavigationProperties=false]
Increase Timeout Settings: The error might be due to a timeout issue. You can increase the timeout settings in Power Query to give it more time to load the data
Simplify the Query: If possible, simplify your query to load only the necessary columns and rows. This can help reduce the complexity and size of the data being processed.
Given it's one event likely the Excel work around for speed
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
