Missing columns when connecting to Dataverse tables

[goisisi]

I have a dataverse solution that includes 5 tables and the beginnings of a canvas app for the purpose of recording data and being able to use Power BI to then visualise that recorded data on the fly.

I am new to the Power Platform, and have looked extensively for solutions to this issue, but noone seems to have the same issue that I am having.

When I export from dataverse to Excel I have no issue, however I then need to recreate all the relationships and the choice names are lost along with the thing not being live.

I have tried importing the data instead of using DataQuery but the same thing happens. I don't even know where to start looking for what might be causing this issue.

These are my dataverse columns (I have filtered for only the ones I created)

And this is what powerquery gives me. (again, filtering for only those I created)

This same thing is happening in each of my five tables. No date or time columns are coming across at all, except the default dataverse ones, which I don't care about for the purpose of reporting. It also drops some text columns that have the default settings from first creation in dataverse.

I am really stuck! If anyone has suggestions, I would love to hear them.

[goisisi]

This is a very comprehensive troubleshooting guide. I will definitely run through these and see what comes of it.

[123abc · Accepted solution]

It seems like you are experiencing issues when connecting to Dataverse tables in Power Platform, particularly when importing the data into Power Query. The problem you described, where columns are missing and certain data is not being transferred correctly, can be caused by a few different factors. Here are some steps you can take to troubleshoot and resolve the issue:

1. Check Dataverse Permissions: Ensure that you have the necessary permissions to access and export data from the Dataverse tables. Insufficient permissions can lead to issues with data extraction.

2. Data Types and Transformations: Check if the columns that are missing in Power Query have custom data types or transformations in Dataverse. Power Query might not be able to interpret these customizations correctly. Try simplifying the column configurations in Dataverse.

3. Data Import Settings: When importing data into Power Query, review the import settings to make sure you are importing all necessary columns and that the data types are correctly recognized. You can edit the query in Power Query Editor to adjust column selections and data type conversions.

4. Check for Filters or Views: Ensure that there are no filters or views applied to the Dataverse tables that could be limiting the data being exported. Verify that you are exporting the entire dataset.

5. Data Refresh: After importing the data into Power Query, try refreshing the data to see if the missing columns and data are retrieved during the refresh process.

6. Review Power Query M Code: Examine the generated Power Query M code to see if there are any specific transformations or filtering steps that might be causing the issue. You can open the Power Query Editor and navigate to the "Advanced Editor" to view and edit the M code.

7. Data Type Matching: Ensure that the data types of the columns in Dataverse match the data types expected by Power Query. In some cases, data type mismatches can lead to data loss or incorrect column recognition.

8. Consider Dataflows: Instead of importing data directly into Power Query, you can create Dataflows in Power Platform. Dataflows allow you to define data transformations and mappings within the Power Platform environment, which may help with column recognition and data consistency.

9. Power BI Desktop: If you intend to use Power BI for visualization, you can also import data directly into Power BI Desktop from Dataverse tables. This can sometimes provide better control over data extraction and transformation.

10. Consult Documentation and Community: Check the official Microsoft documentation for Power Platform and Dataverse for any specific guidance related to data import and connections. Additionally, consider seeking assistance on Power Platform community forums or Microsoft support for more specialized help.

By following these steps and considering the specific configurations and customizations in your Dataverse solution, you should be able to diagnose and resolve the issue with missing columns and data during data import into Power Query or Power BI

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[goisisi]

Ok, soooooo... I got started with the trouble shoot my smallest dataset first and found that in step 2 of your list worked: 

Data Types and Transformations: Check if the columns that are missing in Power Query have custom data types or transformations in Dataverse. Power Query might not be able to interpret these customizations correctly. Try simplifying the column configurations in Dataverse.

---

There were some customisations on columns that when removed, seemed to work, but only on one out of two. the 'customisation' was that it was searchable in dataverse. but that really didn't make any sense as that is the default out of the box for a simple single line of text field. It worked though, so I wasn't complaining, until it didn't work on the next one. I gave up for the day and came back today, removed the two offending columns and re-added them, just changing the display name. It worked! but so did everything else. With no filddling about from me... I am going to mark this as the answer because it definitely is a handy troubleshooting guide.

Thanks for the help @123abc !