Data loads in Power Query but not in Power BI

[IanR]

I have a query that loads sample data in to Power Query but does not load any data into Power BI. I used the advanced editor to grab the M code for the query and used it to recreate the query in another Power BI Desktop file. In that file the query loads data into Power BI.

This may or may not be relevant: In the first file (where the query doesn’t work) it is used as a staging table to bring data in which is appended to another table but not actually required in the final model. I therefore thought that I would save some RAM by unticking the query’s Enable Load option. I noticed when I did this that the that the ‘Include in report refresh’ option became greyed out. I could’t think why this should be the case but I didn’t dwell on it at the time. Now, after noticing that the data is missing from the final table I have turned Enable Load back on so that I can look at the staging data. It turns out that that there isn’t any to be seen in either the Data or Report views.

Is it possible that unchecking Enable Load somehow also turned off ‘Include in data refresh’ and that this being turned off is somehow ‘sticky’ and won’t turn back on again despite the tick in the checkbox?

Is my best bet to completely delete this quesry and start again? This is a painful thing to do because the OData connection that I have to use to get at CRM data makes any test refresh take a little over two hours and makes doing anything in the Power Query interface unpleasantly slow (I have a fast internet connection and a high spec machine but just opening Power Query to look at this model is a give up waiting and go make a coffee experience).

Thanks

Ian

[v-yuezhe-msft]

@IanR,

I am trying to reproduce your issue, however, when I use "Append Queies as New " to append two tables, even if I untick "Enable load" option for one stage table, after I click "Close & Apply" in Query Editor, I can see complete data in the appended query in Data View. Moreover, after I tick "Enable load" option for the stage table and click "Close & Apply", the data appear in stage table in Data View.

In your scenario, do you make sure that the missing data of final table come from the staging table? And after you tick "Enable load" opion for the staging table and click "Close& Apply" , please right-click the "Refresh" button under Home ribbon to re-process data model as other's post, then verify if data appear in the staging table in Data View.

Regards,
Lydia

[Seth_C_Bauer]

@IanR After you enabled the load did you refresh the data in the model? You will need to reprocess the model, and by extension pull in the data now.

In addition, if you are pulling from CRM online (sounds like it) if you remove all the tables, functions, etc that are essentially connections to other entities in CRM your load will be much faster. Only pull in the data from the current entity and then build a model on the entities you've independantly pulled. (Just quick thought as I've dealt with the pain of CRM Online/Odata)

[IanR]

Hi Eno1978,

I have tried to reproduce the problem with much a much smaller model, one that contains one small table and I haven't been able to reproduce it. Either I had forgotten to refresh (several times) as you suggest or it only kicks in with large OData datasets. I'll assume the former for now.

On the subject of OData, can you explain what you mean by 'remove all the tables, functions, etc that are essentially connections to other entities in CRM your load will be much faster'?

Thanks

Ian

[Seth_C_Bauer]

@IanR When you connect to CRM you will notice that there are values in some entities that are highlighted yellow. You can choose columns from these related objects. (This is also a setting in Options that you can disable. Under Current File -> Data Load -> Relationships - "Import relationships from data sources")

These highighted yellow "values" are recognized paths and relationships to other entities within CRM. If you choose the values in this way, your queries to pull the data will slow down exponentially. I either remove the setting, or remove those columns from all my connections in order to speed up the ingestion.

I'm making some assumptions here that you are using these, as without them, things should be fairly quick.

[IanR]

Hi Eno1978,

Do you mean the columns that start off containing 'Record' or 'Table'? Those are the only column contents that I can see that are not in the standard font colour. A couple of questions:

The Table columns I had a hunch that I had to be careful with - as they are looking at other tables in CRM. However, despite the lack of query folding, won't the heavy lifting for these, the looking up in other tables, be done on the source server and therefore be relatively quick?

Clicking on the double headed arrow symbol in the header for the Record columns opens a dialog that offers a list of options; mostly Id, Logical Name, Name and Row Version. For ID columns I don't know of any other way of getting at the data. For example, in the OpportunitySet table all the useful ID columns, such as opportunity ID or parent contact ID, are of the Record type. Without expanding these columns I don't think I could bring these IDs into the model. Ordinarily I would only select Id and possibly Name. I have started to use the Name column less as I can look these values up in the root tables (e.g. use ContactSet table for parent contact name) but I don't see a way of not expanding these to get to the ID. Am I missing a trick?

Thanks

Ian  

[Seth_C_Bauer · Accepted solution]

@IanR Yeah, the column names or values in yellow are what I'm referring to. If you can expand and dig in as you describe that is the functionality that will slow things down exponentially. I can't speak to the "why" or "where" in the processing, all I know is it kills the processing in Power BI.

There should be relationships that you can use in these entities to build relationships within Power BI. For all of the translations from ID to name or description, those you will most likely have to re-create in Power BI. You can build those columns of values within the table, or add them as seperate dimensions if the lists are huge and relate them via the ID.

Another tool I've used to see the actual names vs. what CRM (Dynamics 365) shows is the XRM Toolbox. You can download the schema and it is extremely helpful as it shows you all the value transformations for dropdowns, etc.

I've used the above methods, only pulling data, and everytime the data loads in under 5 minutes.