Power Platform Community Forum Thread Details

Hello,

I am working on a project with multiple data sources with different column headers what I am looking to pull into Power BI. The datasets are big (shipment data) and the difference sources, have different column headers but contain relatively the same data fields (data comes from external suppliers, some sources have more/less columns depending on supplier). Currently, all the data is provided on a quarterly basis in excel spreadsheets from the different suppliers and stored in a Sharepoint.

I started by doing all of the ETL work in a .pbix on Power BI desktop with the goal to take all the different data sources and transform them into one single source that can be used to analyse. Due to the large amount of ETL and the large datasets, I realized this would not work as a long term solution as our database will continue to get larger. My company is very large but not quite there yet, in terms of managing our data in one centralized place like a SQL server or data lake yet so that isn't really an option as of now.

While researching different solutions, I came across Power BI Dataflows and was interested in the mapping to a standard CDM. Then I found out about the Power Platform dataflows and found some info that you can create an Entity in the Power Platform and map data to this Entity creating a single and clean dataset that I could then import to Power BI using the Power Platform dataflow.

I was able to create an Entity and added the fields that I need for the mapping. Then I created a new Power Platform dataflow but when tried mapping the different data sources to the Entity, I got an error saying I was trying to map 2 data sources to the same entity (which was the whole point of me doing this).

So I have a few questions:

Does this approach make sense?
- If yes, your assistance on how I can fix this would be much appreciated
If not, is there a better way I should be trying to do this?

Thank you and let me know if you need any additional information.

Ted

Categories:

Microsoft Dataverse with Power Apps

Hi @file_theo

I'm not a Power BI expert but what you are trying to do with dataflow and the Common Data Model makes sense. I assume that you are ticking the Analytical entities only box when you create a new dataflow?

It sounds like you have two different dataflows for the same entity. What you need to do is have one dataflow which combines the 2 different sources into one and then map the combined data to an entity.

For example, in the screen shot below, I have 2 different tables of data for a Building entity. I can use the Power Query combine transformations to join these 2 queries into a single table. In this instance the Append queries action worked for me. From here you have a single table to map to your entity with all of the data combined.

Annotation 2020-04-15 061822.png