Query Expression error "We cannot convert a value of type Record to type Table"

[MichaelHutchens]

Hi folks, I'm struggling with the error below in a query. There are a few posts like this floating around but the answers seem to be pretty contextual so I thought I'd post a new one:

Here's my code:

let
    Source = Table.Combine(
        {
SharePoint.Tables("https://URL/sites/change-proposals/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/council-news/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/department-news/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/how-we-work/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/about-me/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/my-council-journey/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/our-locations/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/policies/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/teams-groups/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/tools-to-do-my-job/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/wellbeing-and-safety/", [Implementation="2.0", ViewMode="All"]),
SharePoint.Tables("https://URL/sites/who-we-are/", [Implementation="2.0", ViewMode="All"])
        }
    ),
    #"Removed Columns" = Table.RemoveColumns(Source,{"odata.type"}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns", {"Items"}),
    #"Expanded Items" = Table.ExpandTableColumn(#"Removed Errors", "Items", {"Author Byline", "Authoring Canvas Content", "Checked Out To", "Comment count", "Content Type", "Created", "Created By", "Description", "Expiration date", "File Size", "First Published Date", "Like count", "Modified", "Modified By", "Name", "Page Layout Content", "Review Deadline", "Title", "Topic", "Version"}, {"Items.Author Byline", "Items.Authoring Canvas Content", "Items.Checked Out To", "Items.Comment count", "Items.Content Type", "Items.Created", "Items.Created By", "Items.Description", "Items.Expiration date", "Items.File Size", "Items.First Published Date", "Items.Like count", "Items.Modified", "Items.Modified By", "Items.Name", "Items.Page Layout Content", "Items.Review Deadline", "Items.Title", "Items.Topic", "Items.Version"}),
    #"Expanded Items.Topic" = Table.ExpandTableColumn(#"Expanded Items", "Items.Topic", {"Label", "TermID"}, {"Items.Topic.Label", "Items.Topic.TermID"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Items.Topic",{"ItemKind", "ItemName", "IsLeaf"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Removed Columns1",{"odata.etag", "odata.editLink", "Items@odata.navigationLinkUrl"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns2", each ([Items.Content Type] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "odata.id", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"odata.id.1", "odata.id.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"odata.id.1", type text}, {"odata.id.2", type text}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Changed Type",{"odata.id.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns3", "odata.id.1", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"odata.id.1.1", "odata.id.1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"odata.id.1.1", type text}, {"odata.id.1.2", type text}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Changed Type1",{"odata.id.1.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns4", "odata.id.1.1", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"odata.id.1.1.1", "odata.id.1.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"odata.id.1.1.1", type text}, {"odata.id.1.1.2", type text}}),
    #"Removed Columns5" = Table.RemoveColumns(#"Changed Type2",{"odata.id.1.1.2"}),
    #"Added Suffix" = Table.TransformColumns(#"Removed Columns5", {{"odata.id.1.1.1", each _ & "/SitePages/", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Added Suffix",{{"odata.id.1.1.1", "URL"}}),
    #"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"URL", "Items.Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Removed Columns6" = Table.RemoveColumns(#"Merged Columns",{"Id", "Title"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns6",{{"Merged", "URL"}, {"Items.Topic.Label", "Label"}, {"Items.Topic.TermID", "Label ID"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",null,"",Replacer.ReplaceValue,{"Label"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"Label ID"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Replaced Value1", {{"Label", type text}, {"Label ID", type text}}),
    #"Expanded Items.Modified By" = Table.ExpandListColumn(#"Changed column type", "Items.Modified By"),
    #"Expanded Items.Modified By1" = Table.ExpandRecordColumn(#"Expanded Items.Modified By", "Items.Modified By", {"title"}, {"Items.Modified By.title"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Items.Modified By1",{{"Items.Modified By.title", "Last Modified By"}, {"Items.Modified", "Last Modified Date"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Last Modified Date", type datetime}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Changed Type3",{{"Items.Like count", Int64.Type}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Changed Type4",null,"",Replacer.ReplaceValue,{"Items.Like count"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,"",Replacer.ReplaceValue,{"Items.First Published Date"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Replaced Value3",{{"Items.First Published Date", type datetime}}),
    #"Removed Columns7" = Table.RemoveColumns(#"Changed Type5",{"Items.File Size"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns7",{{"Items.Like count", "Like count"}, {"Items.First Published Date", "First Published Date"}}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Items.Expiration date", type date}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Changed Type6",{{"Items.Description", "Description"}}),
    #"Expanded Items.Created By" = Table.ExpandListColumn(#"Renamed Columns4", "Items.Created By"),
    #"Expanded Items.Created By1" = Table.ExpandRecordColumn(#"Expanded Items.Created By", "Items.Created By", {"title"}, {"Items.Created By.title"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Expanded Items.Created By1",{{"Items.Created By.title", "Created By"}}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Renamed Columns5",{{"Created By", type text}}),
    #"Renamed Columns6" = Table.RenameColumns(#"Changed Type7",{{"Items.Created", "Created Date"}}),
    #"Changed Type8" = Table.TransformColumnTypes(#"Renamed Columns6",{{"Created Date", type datetime}}),
    #"Renamed Columns7" = Table.RenameColumns(#"Changed Type8",{{"Items.Content Type", "Content Type"}}),
    #"Changed Type9" = Table.TransformColumnTypes(#"Renamed Columns7",{{"Content Type", type text}, {"Items.Comment count", Int64.Type}}),
    #"Renamed Columns8" = Table.RenameColumns(#"Changed Type9",{{"Items.Comment count", "Comment count"}}),
    #"Replaced Value4" = Table.ReplaceValue(#"Renamed Columns8",null,0,Replacer.ReplaceValue,{"Comment count"}),
    #"Renamed Columns9" = Table.RenameColumns(#"Replaced Value4",{{"Items.Checked Out To", "Checked Out To"}}),
    #"Changed Type10" = Table.TransformColumnTypes(#"Renamed Columns9",{{"Items.Authoring Canvas Content", type text}}),
    #"Fix line values - Author byline" = Table.TransformColumns( #"Changed Type10",

{"Items.Author Byline", each if Value.Is(_, type table) then _ else null}),
    #"Expanded Items.Author Byline" = Table.ExpandTableColumn(#"Fix line values - Author byline", "Items.Author Byline", {"title"}, {"Items.Author Byline.title"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Expanded Items.Author Byline",null,"",Replacer.ReplaceValue,{"Items.Author Byline.title"}),
    #"Fix line values - Checked Out To" = Table.TransformColumns( #"Replaced Value5",

{"Checked Out To", each if Value.Is(_, type list) then _ else null}),
    #"Expanded Checked Out To" = Table.ExpandListColumn(#"Fix line values - Checked Out To", "Checked Out To"),
    #"Expanded Checked Out To1" = Table.ExpandRecordColumn(#"Expanded Checked Out To", "Checked Out To", {"title"}, {"Checked Out To.title"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Expanded Checked Out To1",null,"",Replacer.ReplaceValue,{"Checked Out To.title"}),
    #"Changed Type11" = Table.TransformColumnTypes(#"Replaced Value6",{{"Checked Out To.title", type text}, {"Items.Author Byline.title", type text}}),
    #"Renamed Columns10" = Table.RenameColumns(#"Changed Type11",{{"Items.Author Byline.title", "Author Byline"}, {"Checked Out To.title", "Checked Out To"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns10",{"URL", "Label ID", "Label"}),
    #"Removed Columns8" = Table.RemoveColumns(#"Removed Other Columns",{"Label"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns8",{{"URL", Order.Ascending}})
in
    #"Sorted Rows"

Any help would really be appreciated 🙂

[PC2790 · Accepted solution]

Hi @MichaelHutchens ,

It will be very difficult to track  the break point with the given information.

Try to locate the point of failure by going in Power Query and going through each step in the Applied Steps section.That way you will be able to channelise your scope of analysis.

If possible, share your pbix file with the sample data, removing all the sensitive information.

[MichaelHutchens]

Thanks for the feedback @PC2790 , much appreciated.