Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a table that I'm pulling off an API - when I try to load the table - Close & Apply - I get the message "OLE DB or ODBC error: [Expression.Error] We cannot convert a value of type Record to type Table.." I've had a look around on the forum but can't really see a fix. The data, etc is fine in Power Query, it's only when I go to load it to the front-end that I hit an issue.
A bit of history on the data: the API is a third-party client we use for an event. The event was paused before lockdown and is now starting up again. Previously this loaded fine, but due to some changes on the API, I've had to change the end point slightly by appending &limit=10000&page=1 to the URL. I don't know if this change is the reason for the error, or it's something else.
Here's my M:
let
Source = Json.Document(Web.Contents("https://xxx.com/api/v2/pages?campaign_id=gb-xxx&limit=10000&page=1 ")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id", "uuid", "slug", "gift_aid_eligible", "charity_uid", "charity_uuid", "campaign_uid", "campaign_uuid", "owner_uid", "owner_type", "uid", "state", "target_cents", "name", "team_uid", "team_member_uids", "team_leader_page_uid", "expires_at", "amount", "cached_offline_amount_cents", "campaign_date", "fitness_activity_overview", "story", "image", "team_role", "created_at", "updated_at", "activated_at", "url", "campaign_name", "charity_name", "team_page_id", "pledge_url", "tags", "fitness_activities_totals", "fitness_goal", "donation_url", "direct_marketing_consent", "country_code", "invitation_id", "coordinate"}, {"id", "uuid", "slug", "gift_aid_eligible", "charity_uid", "charity_uuid", "campaign_uid", "campaign_uuid", "owner_uid", "owner_type", "uid", "state", "target_cents", "name.1", "team_uid", "team_member_uids", "team_leader_page_uid", "expires_at", "amount", "cached_offline_amount_cents", "campaign_date", "fitness_activity_overview", "story", "image", "team_role", "created_at", "updated_at", "activated_at", "url", "campaign_name", "charity_name", "team_page_id", "pledge_url", "tags", "fitness_activities_totals", "fitness_goal", "donation_url", "direct_marketing_consent", "country_code", "invitation_id", "coordinate"}),
#"Expanded tags" = Table.ExpandListColumn(#"Expanded Value1", "tags"),
#"Expanded amount" = Table.ExpandRecordColumn(#"Expanded tags", "amount", {"cents"}, {"cents"}),
#"Expanded coordinate" = Table.ExpandRecordColumn(#"Expanded amount", "coordinate", {"lat", "lon"}, {"lat", "lon"}),
#"Added Custom" = Table.AddColumn(#"Expanded coordinate", "Target Amount (£)", each [target_cents]/100),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name", "uuid", "gift_aid_eligible", "charity_uid", "charity_uuid", "campaign_uuid", "owner_uid", "team_member_uids", "team_leader_page_uid", "expires_at", "cached_offline_amount_cents", "campaign_date", "fitness_activity_overview", "story", "image", "team_role", "created_at", "updated_at", "campaign_name", "charity_name", "team_page_id", "pledge_url", "tags", "fitness_activities_totals", "fitness_goal", "donation_url", "direct_marketing_consent", "country_code", "invitation_id"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"cents", "Raised (pennies)"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Raised (£)", each [#"Raised (pennies)"]/100),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Raised (£)", type number}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "activated_at", "activated_at - Copy"),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "activated_at - Copy", Splitter.SplitTextByPositions({0, 10}, false), {"activated_at - Copy.1", "activated_at - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"activated_at - Copy.1", type date}, {"activated_at - Copy.2", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"activated_at - Copy.1", "Activated At"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"activated_at - Copy.2"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each [Activated At] > #date(2019, 12, 31))
in
#"Filtered Rows"
Any ideas on what's causing the issue?
Thanks in advance,
SamB
Solved! Go to Solution.
Hello @Anonymous
its this line thats makes the error
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
As you can see you have in your record two types of values. A list and a record and basically you are trying to expand a list column, even though there is a record in it, and that produces the error.
Hopes this helps
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Anonymous
are you sure that your first step "source" gives back a record?
Source = Json.Document(Web.Contents("https://xxx.com/api/v2/pages?campaign_id=gb-xxx&limit=10000&page=1 ")),
#"Converted to Table" = Record.ToTable(Source),
Because you are immediatly converting it to a table, and it seems that this error can only come from the step "Convert to table".
Select the stept "Source" in Power Query and post a screenshot here
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Jimmy801 ,
Thanks for the input. This is what I see when I go back to Source:
Let me know if I can supply any other pertinent info.
Cheers,
Sam
Hello @Anonymous
its this line thats makes the error
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
As you can see you have in your record two types of values. A list and a record and basically you are trying to expand a list column, even though there is a record in it, and that produces the error.
Hopes this helps
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Thank you @Jimmy801 - going back and changing this step solved this issue.
Your assistance is appreciated, cheers!
Sam
FYI - here is updated part of the query:
let
Source = Json.Document(Web.Contents("https://xxx.com/api/v2/pages?campaign_id=gb-xxx&limit=10000&page=1 ")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)...