DataFormat.Error: While importing Google Analytics data for Revenue

[Anonymous]

Hi,

hope someone can help me with that.

I´m trying to import revenue data from our Google Analytics account by date. I have 10 different Sales Lines, some are working just fine but some are runing into an error:

DataFormat.Error: Unerwartete Daten für eine Spalte vom Typ "Currency" erhalten.
Details:
1.6553920699772E7

Means:

DataFormat.Error: Retrieve unexpected data for a Currency column.

Details:
1.6553920699772E7

Does someone know how the handle that kind of error. I assume that the amount is to big, but I´m not aware of any size restrictions for the fields. I also tried to split the date by hour, but this doesn´t helped.

Thanks in advance for any recommendations.

Best

Lina

[hugoberry]

The number looks quite small for currency:

$16,553,920.699772

Can you provide some more details to the query that loads this data? Maybe the precision of this number is too hard to grasp.

[Anonymous]

Hi,

thanks for your reply. The query to get the data looks like this:

= Cube.Transform(#"xxxxxxxxx",

{

{Cube.AddAndExpandDimensionColumn, "ga:deviceCategory", {"ga:deviceCategory"}, {"Device Category"}}, {Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},

{Cube.AddMeasureColumn, "Average Order Value", "ga:revenuePerTransaction"},

{Cube.AddMeasureColumn, "Ecommerce Conversion Rate", "ga:transactionsPerSession"},

{Cube.AddMeasureColumn, "Revenue", "ga:transactionRevenue"},

{Cube.AddMeasureColumn, "Transactions", "ga:transactions"},

{Cube.AddMeasureColumn, "Sessions", "ga:sessions"}

})

When I checked the value in GA directly I get also the currency written before the value. I tried a variable which does not include the currency but this doesn´t work either.

Thanks

Lina

[Phil_Seamark]

Just tried your query but using my own GA account and it worked fine.  Maybe it's regional settings or something up with GA.

What are your regional settings in Power BI desktop set to?

[Anonymous]

Hi,

in PowerBI desktop my regional setting is Germany.

Thanks

[Phil_Seamark]

Give it a crack on US just to see if it works any better (I know there will be other shortcomings)

[Anonymous]

changed it to US, but still not working. 

[hugoberry]

I was able to replicate the error with

= Currency.From("1.6553920699772E7","de-DE")

it is fixed with 

= Currency.From("1.6553920699772E7","en-US")

or

= Currency.From(Number.From("1.6553920699772E7"),"de-DE")

[Anonymous]

Hi,

thanks for your reply, I didn´t had the time to test it. I found a work arround using a different variable. I will test it as soon as I have time.