Data Extraction & Storage

Power Query Data Setup
1

Hi Folks,

I have a connection to a database which stores utilisation for users, due to the volume of Data and the method of connection our IT team says that we must query this data month at a time which is not ideal. Normally i would just query all the current live data and bring into PBI but now i will have to extract and store the data by month and then import into PBI.

I am therefore trying to figure out the best way to do this so the end result my data can be imported into my Power BI report.

Current solutions going through my mind are:

  1. Create a flow to extract the data and convert to CSV and store in a SharePoint folder
  2. Create a flow to extract the data and store in a SharePoint list
  3. Create a flow to extract the data and store in Dataverse

Problems to consider:

  1. User needs to specify the month the data relates to
  2. Process needs to work out whether the data covers a duplicate period and if so overwrite but if not add as new
  3. If i opt for database to CSV should i create a CSV file for each month or 1 file and get the flow to add the lines to the bottom of the existing sheet.

I have not done a flow this complex before so any thoughts guidance would be appreciated before i go down a rabbit hole! I’m also open to any other solutions that does not involve Power Automate.

Thanks folks.

2

You can create a staging area on database - db, so that you can do your transformation before loading into power bi

or I prefer you use the
1 - Create a flow to extract the data and convert to CSV and store in a SharePoint folder

2 Likes
3

Hi @adesinamk thanks for your reply, I am testing out a flow to CSV so hopefully this works

4

Wow, it’s been a year since this thread started! I hope you’ve made some progress with your data extraction and storage challenges. I think creating a flow to extract the data and convert to CSV and store in a SharePoint folder is a good idea. It’s always good to have a backup of your data, especially if you’re dealing with a large volume. As for the problems you mentioned, have you thought about creating a dropdown menu where the user can select the month the data relates to? Also, I think you might really like Nannostomus. It’s a great service that can definitely make data extraction easy and accessible for every customer. It might be something you need.