cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenCardineau
New Member

Create a dataflow pulling CDS data (from Project Task Table, linked to P4W) and merging them with Excel data

Hi all!

I'm more at ease with PBI dataflows, but need your support here I'm afraid...

I'd like to create a dataflow in Power Apps which would combine (merge) some data from CDS (from the Project Task table, pulled from Project for the Web app) and some data from Excel spreadsheets (hosted on OneDrive, so I guess also available on CDS...), so that I can use this dataflow in some Excel power query.

 

1) does it makes sense to you?

2) I can see the Project Task table in CDS, but how do you create a dataflow using already CDS-hosted data (what connector do you use?)

 

Thx in advance for your help!

Ben

3 REPLIES 3
HSheild
Super User
Super User

Hi @BenCardineau 

 

I think the approach here would be to not have the Project Task table from Dataverse as a source.  Instead, just have Excel as the source and use dataflow to map that data into the existing Project Task table in Dataverse.  You will likely have some sort of unique ID in the Excel table that matches to the Project Task - this should be set as an alternate key on the Project Task table in Dataverse and will be used to know this row of the Project Task table to merge the Excel rows into.

 

I hope that makes sense

Thx a lot @HSheild. I agree, your proposal could work... However, my constraint is that I need to keep the overall approach to use best capabilities from P4W in terms of collaboration (more robust then excel files). 

 

I've been trying different options in the meantime.

1) trying the migrate the CDS data to an Azure data lake, and then to create a dataflow in Power Apps linking to this data lake, but for some reason I can't seem to see the Project Tasks in the list in 'new link to data lake'. Why? I can see this table in Tables list, but not in new link to data lake...

 

2) create in Excel query editor an Odata link to Project Online (not project for the web). That works, but could that be applicable to P4W (not Project Online)?

 

Thx for your help,

Ben

poweractivate
Community Champion
Community Champion

@BenCardineau 

 

In case it helps, note that Common Data Service can be used directly in a Dataflow, but only when the Dataflow is being created from Power BI service, not from Power Apps.

 

When creating the Dataflow from Power Apps, the option to use a Dataverse (Common Data Service) directly as a Dataflow Data Source is not an option at all from there. 

 

e] Power Apps.png

 

It is only an option to use a Dataverse (Common Data Service) as a Dataflow Data Source, from Power BI dataflow only specifically, as per the below, then you can actually use a Dataverse (Common Data Service) directly as a datasource, but this can be done only from a Power BI dataflow specifically, see as follows:

 

e] Power BI.png

 

 

 

In case this helps you, we wanted to let you know of this very specific aspect of the Power Apps versus Power BI dataflow.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,325)