cancel
Showing results for 
Search instead for 
Did you mean: 

CDS direct query from Excel Power Query

We now should have the ability to use CDS as a data source for Power BI, which is great.

However, we do not yet have the ability to use CDS as a data source for Excel Power Query (aka Get & Transform).

This would be the most direct way of producing pivot tables and printable reports from CDS data.

Please vote this up!

Status: Under Review

@jonoluk to confirm status pls.

Comments
Level 10

I also think this is a much-needed feature especially because PowerBI doesn't support pivot tables. (I am aware of Matrix tables but they aren't as good as pivot tables) 

Flow Staff
Status changed to: Under Review

@jonoluk to confirm status pls.

Level: Powered On

I would like to use PowerApps and the CDS more, but the difficulty in creating reports is dramatically reducing the benefits of PowerApps and the CDS.  It is also very critical that reporting tools (such as Excel and PowerBI) must be able to report on Custom entities, not just the entities provided.

Level 10

Hi @jonoluk

Is there any update on status?  I heard today that Access Web Apps are being discontinued and the inability to query CDS from Excel is the only thing that is really stopping me from migration to CDS.

Otherwise I will have to look for alternatives.

Thanks.

Microsoft Employee

Hi Meneghino!

 

We have an Excel Add-In that lets you manage your CDS data from Excel.  You can find more details on this here: https://powerapps.microsoft.com/en-us/blog/powerapps-cds-ga/.  It can be launched directly from the CDS portal.

 

Can you take a look and let me know whether that meets your needs?  What did you need to do from Excel specifically?

 

@EdRGray- Good news!  Power BI works with CDS now.  The Power BI integration feature is in preview per the blog entry here: https://powerapps.microsoft.com/en-us/blog/cdsconnectortopowerbi/.  Take a look and let me know if that works for you!

 

All best,

jono

 

Level 10

Hi @jonoluk, thanks for the reply.

I am well aware of the Excel Add-in but no it does not meet my needs.

What I and others need is to be able to connect to CDS from Excel via Power Query (a.k.a. Get&Transform) so that we can do some ETL and then create advanced printable reports with pivot tables.

Is the requirement clear? Do you have any visibility of when this might happen?

Thanks again for the effort.

Level: Powered On

To jonoluk

 

The current PowerBI connection to CDS is basically garbage and unusable.  It only returns the 'Standard' Entities and does not return any custom fields add to those.  Exactly how many applications can use the 'Standard' entities without also needing 'Custom' entities and custom fields?  For me, zero!  Therefore, the current PowerBI connection might as well not even exist because probably no one can use it.  Also, please ensure the we will be able to add VIEWs to CDS and have access to those VIEWs from Excel and Power BI.  Thanks!

Level: Powered On

@jonoluk

 

This is also stopping me from migrating to CDS. Any updates on this connection? is also the ability to retrieve the custom entities as mentioned by @EdRGray in the pipeline?

Level 10

Looking forward to see this idea REAL

Level: Powered On

Definitely needed!