Automatically generating flat data

[Gregwoolley2387]

Hello,

I'm fairly new to Power BI, so apologies if this is a stupid question.

I've been able to build dahboards to date for 'one-off data', however I would now like to create a dashboard which will update according to a fairly contuous data collection. So for example, I will be setting up a dashboard to reflect the current status of a Investment Fund (e.g. money disbursed, money received, performance of clients etc). I can do this for the data I have at this present moment. However, this data will need to be updated on a monthly basis.

Sources of data will be Google Forms and data from Salesforce.

So my main quandry is:

1.  whether I can automatically convert data downloaded from a Google form and Salesforce into a format / structure which is useable in Power BI (i.e. a flat data strcuture).
   1. (If possible I want to avoid manual data manipulations.)
2. Does Power Query Editor have role here? (I've never used, and find it a little intimidating!) Or would this still require a manual intervention each month?
3. Also, where would it best to 'store' the consolidated data? Traditionally I would have done this in Excel. Perhaps it would be better in a Google Form or can it be in Power BI itself?

Really appreciate any guidance any one can give here.

Best wishes, Greg 

[v-yuezhe-msft]

@Gregwoolley2387,

Firstly, is there any possiblity that you transfer the data from Google Forms to Google sheet? You can connect to Google sheet in Power BI Desktop following the guide in this similar thread. In the same Power BI Desktop file(PBIX file), you can import Salesforce data  using Salesforce object connector.

Secondly, what role do you refer to? In Power BI Desktop, you can configure Row Level security.

Thirdly, when you import data from data sources into Desktop and create report in Power BI Desktop, the consolidated data is stored in the PBIX file. If you publish the PBIX file to Power BI Service, Power BI manages the storage of data and metadata using Azure BLOB and Azure SQL Database.

 


Regards,
Lydia

[Gregwoolley2387]

Hi Lydia,

Thank you for taking the time to respond, really appreciate it!

1. Yes definitely, we will be using Google Sheets. I should have written that will be taking the results from Goofle Forms into Google Sheets. And thank you for the note on the Salesforce Object connector.

2. By 'role' I was trying to work out whether the Query Editor could be used to automate the manipulation of the 'raw data' flowing through from Google Sheets and Salesforce. (The reason I ask this is because the data from Google Sheets and Salesforce will not be 'Power BI friendly', i.e. it will be in a large 'cross table'. My understanding is that teh dataa needs to be 'flat data' to be easily compatible with the majroity of chart sin Power BI.

3. Thank you this explanantion.

Look forward to hearing from you on any other help you can offer. 

[v-yuezhe-msft]

@Gregwoolley2387,

I am not sure what is the cross table like, but you can transform and shape data in Power BI Desktop query editor.

Regards,
Lydia