Power BI ETL Capabilities

[Joe01100]

Hello All

My company has data that’s being pulled from two separate databases into two separate excel sheets and this is how they stage their data to create reports on excel within these sheets.

The issue is that 1, they want to use power bi and 2 they want everything to be automatic.

Now lets say I figure out the automatic process of being able to pull all this data, stage it into these excel sheets and have a live connection from these data sources into power bi. So that if they make any changes to the excel sheet it would change the reports i create in power bi online. 

Now the question I have….. because these two excel sheets are pulling from different data sources the data has different formatting and requires some ETL processing.

I understand that yes I can transform data within power bi. But If we keep adding new excel sheets with that different formatting, does Power BI have the capabilities online to have some sort of automatic etl processing each time we add in a new data source (new excel sheet)? Or would It require some third party coding or etl processing as we stage the data within excel or some data warehouse. 

I hope this explains my situation well enough. If not please ask any sort of questions.

[mahoneypat]

You can do it all in Power BI w/ no 3rd party tools.  Note that you won't have a "live" connection to the Excel workbooks (although you can to other types of sources), but you can't set up automated refresh multiple times a day based on your license.  To get new worksheets as they are added, assuming each worksheet is formatted the same way, you would likely use a custom function to process each worksheet and then combine all the tables (from each worksheet).

Here is a good video that should help - https://exceleratorbi.com.au/combine-all-sheets-in-a-workbook-with-power-query/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

You can do it all in Power BI. You can read more about Power BI ETL capabilities in this article.