Expression Error - We cannot convert a value of type Record to type Table

[rpiboy]

Hi all,

I have a data model that I have not opened in a couple of months and when I opened it recently I got the following error. I looked things over in the Database and in PowerBI and I see no indication of why it is failing... 😞 All i'm trying to do is expand data from another table.

Below the image I have copy/pasted the full text since it was trimmed by the image. Error occurs on line 6

let
    Source = Table.NestedJoin(workstation,{"workstation_type_id"},workstation_type,{"workstation_type_id"},"NewColumn",JoinKind.LeftOuter),
    #"Sorted Rows" = Table.Sort(Source,{{"workstation_type_id", Order.Ascending}, {"workstation_id", Order.Ascending}}),
    #"computer_name - Uppercased Text" = Table.TransformColumns(#"Sorted Rows",{{"computer_name", Text.Upper}}),
    #"Workstation Type - Renamed Columns" = Table.RenameColumns(#"computer_name - Uppercased Text",{{"NewColumn", "Workstation_Type"}}),
    #"workstation_type - Expanded" = Table.ExpandTableColumn(#"Workstation Type - Renamed Columns", "workstation_type", {"description"}, {"Workstation_Type.description"}),
    #"ServerAlias - Added" = Table.AddColumn(#"workstation_type - Expanded", "ServerAlias", each if [computer_name] = null then "na"
    else if Text.Range([computer_name], 0, 2)="RS"
      then Text.Range([computer_name], 3, 7) else "na"),
    #"ServerAlias - Changed Type" = Table.TransformColumnTypes(#"ServerAlias - Added",{{"ServerAlias", type text}}),
    #"ServerAlias - Trimmed Text" = Table.TransformColumns(#"ServerAlias - Changed Type",{{"ServerAlias", Text.Trim}}),
    #"ServerAlias - Cleaned Text" = Table.TransformColumns(#"ServerAlias - Trimmed Text",{{"ServerAlias", Text.Clean}}),
    #"ServerAlias - Lowercased Text" = Table.TransformColumns(#"ServerAlias - Cleaned Text",{{"ServerAlias", Text.Lower}}),
    #"OfficeCode - Added" = Table.AddColumn(#"ServerAlias - Lowercased Text", "OfficeCode", each if [workstation_type_id]="3"
    then Text.Range([computer_name], 3, 3)
       else "na"),
    #"OfficeCode - Changed Type" = Table.TransformColumnTypes(#"OfficeCode - Added",{{"OfficeCode", type text}}),
    #"OfficeCode - Trimmed Text" = Table.TransformColumns(#"OfficeCode - Changed Type",{{"OfficeCode", Text.Trim}}),
    #"OfficeCode - Cleaned Text" = Table.TransformColumns(#"OfficeCode - Trimmed Text",{{"OfficeCode", Text.Clean}}),
    #"OfficeCode - Lowercased Text" = Table.TransformColumns(#"OfficeCode - Cleaned Text",{{"OfficeCode", Text.Lower}}),
    #"ServerAlias.Location - Merged Queries" = Table.NestedJoin(#"OfficeCode - Lowercased Text",{"ServerAlias"},location,{"Server Alias"},"NewColumn",JoinKind.LeftOuter),
    #"ServerAlias.Location - Renamed Columns" = Table.RenameColumns(#"ServerAlias.Location - Merged Queries",{{"NewColumn", "ServerAlias.Location"}}),
    #"ServerAlias.Location - Expanded ServerAlias.Location" = Table.ExpandTableColumn(#"ServerAlias.Location - Renamed Columns", "ServerAlias.Location", {"Site Location"}, {"ServerAlias.Location.Site Location"}),
    #"ServerAlias.Location - Changed Type" = Table.TransformColumnTypes(#"ServerAlias.Location - Expanded ServerAlias.Location",{{"ServerAlias.Location.Site Location", type text}}),
    #"OfficeCode.Location - Merged Queries" = Table.NestedJoin(#"ServerAlias.Location - Changed Type",{"OfficeCode"},location,{"Office Code"},"NewColumn",JoinKind.LeftOuter),
    #"OfficeCode.Location - Renamed Columns" = Table.RenameColumns(#"OfficeCode.Location - Merged Queries",{{"NewColumn", "OfficeCode.Location"}}),
    #"OfficeCode.Location - Expanded OfficeCode.Location" = Table.ExpandTableColumn(#"OfficeCode.Location - Renamed Columns", "OfficeCode.Location", {"Site Location"}, {"OfficeCode.Location.Site Location"}),
    #"OfficeCode.Location - Changed Type1" = Table.TransformColumnTypes(#"OfficeCode.Location - Expanded OfficeCode.Location",{{"OfficeCode.Location.Site Location", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"OfficeCode.Location - Changed Type1",{"ServerAlias.Location.Site Location", "OfficeCode.Location.Site Location"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"location.Site Location.servers"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"workstation_id"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"computer_name", "Computer Name"}})
in
    #"Renamed Columns"

Thanks,

-R

[MarcelBeug · Accepted solution]

It looks like you want to expand column "Workstation_Type", but you supply column name "workstation_type" (all lower case) as the column to be expanded.

As Power Query is case sensitive, both names should be equally cased.

[rpiboy]

Thanks! I knew it would some silly syntax issue! As you can tell I'm a bit of beginner. Same fix cleaned up a second error in my model. Appreciate the reply.

Regards,

-Robert