Combine several queries to salesforce

[Anonymous]

Hello Team,

I am trying to load big report from salesforce to Power BI, but it drops due to timeout.

So I have created several simular reports in Salesforce (same columns names, but different dates in reports) and I was able to add all these tables to Power BI:

But BI creates several tables, but in fact I need one big table. I did append of all tables to single one, but it doesn't look elegant.

Is any option to create single query which will take data from Salesforce report and create one table only?

One request to report looks like:

let
Source= Salesforce.Reports("https://login.salesforce.com/", [ApiVersion=48]),
#"00O1V000007t" = Источник{[Name="00O1V000007t"]}[Data]
in
#"00O1V000007t"

Thank you!

[QueryWhiz]

Hi, I know this reply is very late, but were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

 There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[Anonymous]

You could keep all the operations within the same query, but basically you would always have the n tables that you download one at a time, to be combined together with the table.combine function. Such a scheme, where instead of table.Fromrecords you have your own function that downloads the table from the website

let
   tab1=Table.FromRecords({
    [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
    [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
    [CustomerID = 3, Name = "Paul", Phone = "543-7890"]
}),

   tab2=Table.FromRecords({
    [CustomerID = 11, Name = "Bob", Phone = "123-4567"],
    [CustomerID = 12, Name = "Jim", Phone = "987-6543"],
    [CustomerID = 13, Name = "Paul", Phone = "543-7890"]
}),

   tab3=Table.FromRecords({
    [CustomerID = 21, Name = "Bob", Phone = "123-4567"],
    [CustomerID = 22, Name = "Jim", Phone = "987-6543"],
    [CustomerID = 23, Name = "Paul", Phone = "543-7890"]
}),

   tab4=Table.FromRecords({
    [CustomerID = 31, Name = "Bob", Phone = "123-4567"],
    [CustomerID = 32, Name = "Jim", Phone = "987-6543"],
    [CustomerID = 33, Name = "Paul", Phone = "543-7890"]
})

 in   Table.Combine({tab1,tab2,tab3,tab4})

[v-kelly-msft]

Hi  @Anonymous ，

Try to use Table.Combine funtion in query editor.

See below example:

The related M codes is as below：

let
    Source = Table.Combine({#"Table (2)",Table})
in
    Source

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hello @v-kelly-msft ,

I did it already, but it's not what I am looking for in fact.

Now I have 8 queries to 8 diffrent reports in salesforce. All reports have same columns, but different data.

I would like to write 1 query to these 8 different reports and receive one combined table.

How do you think is it possible?

[v-kelly-msft]

Hi @Anonymous ,

Why cant Table.Combine  achieve your requirement?As shown in my example,using it,I combined Table(2) and Table as one query for a combined table,am I missing something?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Because when I do data refresh Power BI shows that 8 quearies are running instead of one. Maybe it's stupied, but I don't want to merge tables after quesries. I want to merge it while query is executing 🙂

[Anonymous]

have a look here