GA4 connector not refreshing data due to an unexpected character in the JSON input

[660420id]

@Anonymous Can someone please help me resolve an issue I'm facing? I am using 2.0(Beta) GA4 connector. Sometimes the data gets refreshed but since last Saturday, I keep seeing this error:

DataFormat.Error: We found an unexpected character in the JSON input.
Details:
    Value=\31
    Position=0

I am taking data from different properities, all other properities are refreshing properly, but this is the problem with only one property having the most traffic. I've spent hours trying to fix this, and am now stuck until something gets resolved. This is critical to our business as our stakeholders check the data in the report on a weekly basis and without the data refreshing, we aren't able to make appropriate business decisions.

I'm currently on Version: 2.124.2028.0 64-bit (December 2023). Let me know what other details you need to diagnose and resolve this issue.

[v-xiaoyan-msft]

Hi  @660420id ,

Based on the error message you‘ve provided.There might be an extra character (like a comma or other symbol) in the JSON input that’s causing the error. You could try checking the JSON input in a text editor or JSON validator to see if there are any unexpected characters.
Some users have reported that reducing the amount of data being requested in each query can help. For example, one user resolved the issue by only pulling the past month’s worth of data and splitting that into multiple queries so that each request only asks for 5 days at a time.

You may also need to ensure that both Power BI Desktop and the Google Analytics connector are updated to the latest version.
If the issue persists, you might want to try using a different connector or tool to connect to GA4, such as Google BigQuery.

Best regards.
Community Support Team_Caitlyn

[Shabyman2]

Did you resolve this, the big concern for my company is the connector vs BG is that BG will not how unconsented session figures, so this is going to cause a huge change in sessions and conversions, which are heavy used metrics for budgeting, forecasting, marketing spend etc.

[CoderZen08]

Hi @660420id Seems like the 2.0 beta connector is still presenting some issues. I've had some issues with it as well. As a workaround, maybe you can try to test your connection with a 3rd party tool. I've tried windsor.ai, supemetrics 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 GA4 connector in the data sources list:

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

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[Shabyman2]

I appreciate all the comments about using third party tools, but when a company pays near for capacity servers and has had the ability to use GA3 for years without an issue and with 6 weeks to go a GA4 connector is still in Beta, it is shocking to be told go pay more money to access your data using other tools.

Microsofts support had this to say after we raised a ticket with them:

"Thank you for your continued patience and cooperation. We appreciate the collaboration we’ve had so far and apologies for the delay in relaying the update.

We have received an update regarding the  Connector and have been informed that the general availability of Connector's 2.0  implementation is blocked by Google as their API is still in beta. Once it is updated then only PBI will be releasing its version.

 If you have any questions or need additional information in the meantime, please do not hesitate to reach out."

Which begs the question, how can other tools work fine with this API in beta, but powerbi's errors 9/10. This email is essentially, we don't know why, we can't tell you why and we don't know when it will be out of beta or even when then it might be ready outside. 

[abhiteradata]

DataFormat.Error: We found an unexpected character in the JSON input.

getting below error with only 1 month of GA4 data. Please suggest what to do.

section Section1;
shared GA4_July23_to_Feb24 = let
Source = GoogleAnalytics.Accounts([Implementation = "2.0"]),
#"accounts/xxxxx" = Source{[Id = "accounts/xxxxx"]}[Data],
#"properties/yyyyy" = #"accounts/xxxxx"{[Id = "properties/yyyyy"]}[Data],
#"properties/1" = #"properties/yyyyy"{[Id = "properties/yyyyy"]}[Data],
#"Added Items" = Cube.Transform(#"properties/1", {{Cube.AddAndExpandDimensionColumn, "country", {"country"}, {"country"}}, {Cube.AddAndExpandDimensionColumn, "customEvent:event_action", {"customEvent:event_action"}, {"customEvent:event_action"}}, {Cube.AddAndExpandDimensionColumn, "date", {"date"}, {"date"}}, {Cube.AddAndExpandDimensionColumn, "pageLocation", {"pageLocation"}, {"pageLocation"}}, {Cube.AddAndExpandDimensionColumn, "eventName", {"eventName"}, {"eventName"}}, {Cube.AddAndExpandDimensionColumn, "sessionCampaignName", {"sessionCampaignName"}, {"sessionCampaignName"}}, {Cube.AddAndExpandDimensionColumn, "sessionMedium", {"sessionMedium"}, {"sessionMedium"}}, {Cube.AddAndExpandDimensionColumn, "sessionSource", {"sessionSource"}, {"sessionSource"}},{Cube.AddAndExpandDimensionColumn, "sessionDefaultChannelGroup", {"sessionDefaultChannelGroup"}, {"sessionDefaultChannelGroup"}}, {Cube.AddMeasureColumn, "eventCount", "eventCount"}, {Cube.AddMeasureColumn, "bounceRate", "bounceRate"}, {Cube.AddMeasureColumn, "newUsers", "newUsers"},{Cube.AddMeasureColumn, "totalUsers", "totalUsers"}, {Cube.AddMeasureColumn, "screenPageViews", "screenPageViews"}, {Cube.AddMeasureColumn, "sessions", "sessions"}}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each [date] >= #date(2024, 02, 01) and [date] <= #date(2024, 02, 29))
in
#"Filtered Rows";