Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Budfudder
Helper IV
Helper IV

Missing Column Names From CRM

I'm using Power BI Desktop to connect to our 2016 CRM Online instance. When I load a table the Field list only shows a fraction of the columns that are actually in the table. If I go into Edit Queries, I can see that the query is actually returning the correct number of columns, but when it shows them in the Fields list, many are missing.

 

I've tried this with:

  • Opportunities - we have 202 columns, but only 83 show in the Field list
  • Accounts - 313 columns, 148 in Field list
  • Leads - 230 columns, 114 in Field list

Any suggestions? Am I doing something wrong?

2 ACCEPTED SOLUTIONS

So, you need to use the little arrows in the column header to expand that cell.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

It's because those aren't columns in the same table. They are records from a different entity. Every picklist field in CRM is a lookup to a record in another table. So is every lookup field. Basically if the field isn't free text, date, or number it's probably going to show up in the query editor as a record. That has little to do with Power BI and more to do with the way CRM organizes and stores data.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

12 REPLIES 12
Greg_Deckler
Super User
Super User

I'm not sure it is a good idea to try to import all the columns from CRM although what you are describing is odd. I would highly recommend that you wittle down the number of columns to what is important. I realize that does not fix the behavior but a LOT of those fields in CRM are useless.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

A good suggestion - I can just get the columns I actually need. So I tried it.

 

Unfortunately, it didn't work. I selected 23 columns...and only 8 appeared in the Field list.

 

Clearling there's something wrong. In our setup or in what I'm doing.

Can you give me the fields that you are retrieving and from what table? I'll try to replicate it. We also use CRM Online.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I've done a little more research, and what I found might have been obvious to a more experienced user - I'm new. But in the Query Editor, where it lists all the columns, for some it shows data but for many it just shows the text 'Record' (see attached image).

 

It is the columns which show 'Record' that it never puts into the Field list when I apply the query.

 

What does the 'Record' notation mean? That it for some reason can't actually read the data in that field?Image1.jpg

So, you need to use the little arrows in the column header to expand that cell.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks - that appears to solve the problem. But why is it like that? I now have to go through the Query Editor every time and expand every column from which I actually want data? That's awful.

 

But in any case, thank you for your help.

It's because those aren't columns in the same table. They are records from a different entity. Every picklist field in CRM is a lookup to a record in another table. So is every lookup field. Basically if the field isn't free text, date, or number it's probably going to show up in the query editor as a record. That has little to do with Power BI and more to do with the way CRM organizes and stores data.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for that info - I was unaware.

 

Good community - thanks to both responders to my (it turns out) dumb question.

One other thing to note on this. There is a Content Pack for Power BI Service that does all of this tedious work for you. Sometime in the future, you will apparently be able to download the dataset created by Content Packs.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler how useful is that content pack if you have a bunch of customizations? Like if we have a bunch of our own additional custom entities for instance, are those be accessible through the content pack? We're on-premise right now but they're talking about going online, so I'm a little worried about my future.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Well, right now the content pack isn't very useful if you have a bunch of customizations. Meaning you have to create it from scratch. However, my hope is that once you can download a content pack dataset to Desktop then you could create additional queries to import your custom stuff and marry it to the tables in the content pack dataset. That's the thought anyway.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

That's what I was afraid of. Oh well, we're rebuilding our entire CRM from scratch so I'm going to have to redo all the reports no matter what. Thanks for the info. I'll definitely keep an eye on that.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.