CDS direct query from Excel Power Query

[Meneghino]

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!

[afernando]

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) 

[Audrie-MSFT]

@jonoluk to confirm status pls.

[EdRGray]

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.

[Meneghino]

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.

[jonoluk]

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

[Meneghino]

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.

[EdRGray]

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!

[RicardoDiaz]

@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?

[Yahya]

Looking forward to see this idea REAL

[Alex_]

Definitely needed!