Salesforce Report Connector not working for double datatypes

[gerbrandspaans]

We are trying to import a Salesforce report into PowerBI using the built in PowerBI connector. We chose the report we want to ingest, click go and get an error. We checked the data type for the retreived column that reported the error at it is reported as "double", but the error indicates that it is trying to store the data as an integer.

We have no way of editing the column datatype in PowerBI so we are stuck and cannot load the information in PowerBI. Please let us know how to proceed.

The Saleforce Report (Dutch Locale)

PowerBI Error:

Salesforce API description of the data in the report:

...
"reportExtendedMetadata" : 
{ 
	"aggregateColumnInfo" : 
	{ 
		"a!DURATION" : 
		{ 
			"dataType" : "double", 
			"label" : "Average Duration" 
		}, 
		"RowCount" : 
		{ 
			"dataType" : "int", 
			"label" : "Record Count" 
		} 
},...

[v-yuezhe-msft]

@gerbrandspaans,

What is the region setting in your computer?


Please open advanced Editor of your query in Power BI Desktop query editor, add a step in the source code. if you get any errors, please share the source code here.

#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Avg DURATION", type number}})

Regards,
Lydia

[gerbrandspaans]

Hi Lydia,

Hi @v-yuezhe-msft thanks for the help.

My region settings were set to display settings default, and I've set it to match the English (US) setting.

Below is my source code. I'm using the connector, and it seems to get the report list from SF, and then the report, then immediatly throws an error. This means that adding the line you suggest does not help.

let
    Source = Salesforce.Reports("https://test.salesforce.com"),
    #"00O1l000000FFx8EAG" = Source{[Name="00O1l000000FFx8EAG"]}[Data],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Avg DURATION", type number}})
in
    #"Changed Type1"

This still gives me new error that the column is not found, most likely due to the workings of the connector.

Also tried to match the column name to that of the label in the API response. No dice, though.

Kind regards,

[v-yuezhe-msft]

@gerbrandspaans,

How about you change region setting of your computer to Dutch? Please also update your Power Query code to the following:

let
    Source = Salesforce.Reports("https://test.salesforce.com"),
    #"00O1l000000FFx8EAG" = Source{[Name="00O1l000000FFx8EAG"]}[Data],
    #"Changed Type1" = Table.TransformColumnTypes(#"00O1l000000FFx8EAG",{{"Avg DURATION", type number}})
in
    #"Changed Type1"

Regards,
Lydia

[gerbrandspaans]

I've worked out the name for the column ("Duration"), but neither the options you have given (locale settings) nor even removing the column works. Still not working.

Edit: I've also restarted the computer to make sure the changes were made into effect, and restarted PowerBI. This still does not work, neither for US or Dutch locales.

[v-yuezhe-msft]

@gerbrandspaans，

Please open a ticket in Power BI Support site.


Regards,
Lydia