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

(1) Share

Report

Posted on by BenCardineau

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

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Hamish Sheild 3,005 Most Valuable Professional on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
BenCardineau 4 on at

Like (0)

Report

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

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

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report

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

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

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:

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

Was this reply helpful? Yes No