Table and column name retrieval for Power BI

(0) Share

Report

Posted on by JeroenHD

Hello,

I hardly have any experience with Dynamics CRM. For a data model in Power Bi i am in need of a table i which hosts a field which can be seen in the front end.
I would like to link it to the opportunities table.

On the backend i am not able to find it.

Does anybody know the best way to retrieve the table and column involved?

Any help is appreciated.

Kind regards,
Jeroen

All responses (11)

Answers (3)

meelamri 13,204 User Group Leader on at

Like (0)

Report

RE: Table and column name retrieval for Power BI

Glad you managed to solve your problem. With pleasure, do not hesitate to come back on the forum for other questions.
JeroenHD 10 on at

Like (1)

Report

RE: Table and column name retrieval for Power BI

That did the trick indeed! Thanks a heap Mehdi! You are a lifesaver!
Verified answer

meelamri 13,204 User Group Leader on at

Like (0)

Report

RE: Table and column name retrieval for Power BI

Hi,

I guess I figured out the trick. In fact you have to select also the join columns by using an expand in your odata query.
JeroenHD 10 on at

Like (0)

Report

RE: Table and column name retrieval for Power BI

This might sound strange, but the only columns i see with 'Customer' in it are the ones on the image.

Customerid_value returns id string.
Customerid_account returns an exandable column in Power BI which returns the same error as described in the previous post.

Kind regards,
Jeroen
meelamri 13,204 User Group Leader on at

Like (0)

Report

RE: Table and column name retrieval for Power BI

Hi Jeroen,

You are welcome partner. Please try with "customerid" columun.
JeroenHD 10 on at

Like (0)

Report

RE: Table and column name retrieval for Power BI

Hi Mehdi,

thanks for your swift replies. I would love to check if i can get it, but in this case i don't know what column to refer too? Which one should i have here?

Kind regards,

Jeroen
meelamri 13,204 User Group Leader on at

Like (0)

Report

RE: Table and column name retrieval for Power BI

Hi Jeroen,

I'm sorry, I'm not a Power BI expert. I'm afraid I can't help. Could you please test the same thing with the "Account2" column?
JeroenHD 10 on at

Like (0)

Report

RE: Table and column name retrieval for Power BI

Hello Mehdi,

Thanks again. In Power Query it allowed me to add an expandable column named pdw_SalesRep. Only thing is now when i try to unlock these items it returns an error. Do you have any suggestions what might be the case or should i check with the Power Bi Community?

Thanks for helping me locating the column!

DataFormat.Error: We expected a property 'pdw_salesrep', but the OData service omitted it from the response data. This can occur for a number of reasons and does not necessarily imply that the data does not exist or is of a default value.
Details:
Property=pdw_salesrep

Kind regards,
Jeroen
Verified answer

meelamri 13,204 User Group Leader on at

Like (0)

Report

RE: Table and column name retrieval for Power BI

Hi,

Yes, you are right. You are looking for the pdw_SalesRep Column. Please don't forget to verify my answer by ticking YES. It will help others to find the right answer.
JeroenHD 10 on at

Like (1)

Report

RE: Table and column name retrieval for Power BI

Hello Mehdi,

Thanks for your reply and the solution provided. I think the below highlighted in red is what i'm after?

SelectStart*/select \"opportunity0\".[Name] as \"name\"\r , \"opportunity0\".[TransactionCurrencyId!id] as \"transactioncurrencyid!id\"\r ,

\"opportunity0\".[TransactionCurrencyId!logicalname] as \"transactioncurrencyid!logicalname\"\r , \"opportunity0\".[TransactionCurrencyId!name] as

\"transactioncurrencyid!name\"\r , \"opportunity0\".[EstimatedValue] as \"estimatedvalue\"\r , \"opportunity0\".[EstimatedCloseDate] as \"estimatedclosedate\"\r ,

\"opportunity0\".[CustomerId!id] as \"customerid!id\"\r , \"opportunity0\".[CustomerId!logicalname] as \"customerid!logicalname\"\r , \"opportunity0\".[CustomerId!name] as

\"customerid!name\"\r , \"opportunity0\".[CloseProbability] as \"closeprobability\"\r , \"opportunity0\".[OpportunityRatingCode] as \"opportunityratingcode\"\r ,

\"opportunity0\".[pdw_OpportunityNumber] as \"pdw_opportunitynumber\"\r , \"opportunity0\".[pdw_ActualQuoteDate] as \"pdw_actualquotedate\"\r , \"opportunity0\".[qpw_machinetype!id] as

\"qpw_machinetype!id\"\r , \"opportunity0\".[qpw_machinetype!logicalname] as \"qpw_machinetype!logicalname\"\r , \"opportunity0\".[qpw_machinetype!name] as \"qpw_machinetype!name\"\r ,

\"opportunity0\".[pdw_ProgressStage] as \"pdw_progressstage\"\r , \"opportunity0\".[pdw_ProjectProbability_optionset] as \"pdw_projectprobability_optionset\"\r ,

\"opportunity0\".[qpw_Probability_optionset] as \"qpw_probability_optionset\"\r , \"opportunity0\".[pdw_GrossMargin] as \"pdw_grossmargin\"\r , \"opportunity0\".[new_Products_Quantity] as

\"new_products_quantity\"\r , \"opportunity0\".[pdw_SalesRep!id] as \"pdw_salesrep!id\"\r , \"opportunity0\".[pdw_SalesRep!logicalname] as \"pdw_salesrep!logicalname\"\r ,

\"opportunity0\".[pdw_SalesRep!name] as \"pdw_salesrep!name\"\r , \"opportunity0\".[StepName] as \"stepname\"\r , \"opportunity0\".[OpportunityId] as \"opportunityid\"\r ,

\"opportunitycustomeridcontactcontactid\".[EMailAddress1] as \"opportunitycustomeridcontactcontactid.emailaddress1\"/*SelectEnd*/ /*FromStart*/from Opportunity as

\"opportunity0\" \t/*JoinStart*/ left outer join Contact as \"opportunitycustomeridcontactcontactid\" on (\"opportunity0\".[customerid!id] =

\"opportunitycustomeridcontactcontactid\".[ContactId])/*JoinEnd*//*FromEnd*/ /*WhereStart*/where (\"opportunity0\".[StateCode] = 0 and (\"opportunity0\".[ownerid!id] =

\"!eq-userid!\" or \"opportunity0\".[pdw_accountmanager!id] = \"!eq-userid!\" or \"opportunity0\".[pdw_areasalesmanager!id] = \"!eq-userid!\" or \"opportunity0\".[new_salesengineer!id] =

\"!eq-userid!\"))/*WhereEnd*/ /*OrderByStart*/order by \"opportunity0\".[EstimatedCloseDate] asc/*OrderByEnd*/","columnsetxml":null,"organizationtaborder":null,"_modifiedonbehalfby_value":null,

"queryappusage":null,"advancedgroupby":null,"conditionalformatting":null,"description":null,"iscustomizable":{"Value":true,"CanBeChanged":true,"ManagedPropertyLogicalName":"iscustomizableanddeletable"},

"canbedeleted":{"Value":false,"CanBeChanged":true,"ManagedPropertyLogicalName":"canbedeleted"}}]}

Kind regards,
Jeroen