Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Error On Load Table: We cannot convert a value of type Record to type Table.

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

1 ACCEPTED 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

View solution in original post

4 REPLIES 4
Jimmy801
Community Champion
Community Champion

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

Anonymous
Not applicable

Hi @Jimmy801 ,

 

Thanks for the input. This is what I see when I go back to Source:

 

SamBerger_0-1599743094353.png

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

Anonymous
Not applicable

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)...
 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors