cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rpiboy
Frequent Visitor

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

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 6PowerBI_ExpError.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Community Champion
Community Champion

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

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.

Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
MarcelBeug Community Champion
Community Champion

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

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.

Specializing in Power Query Formula Language (M)

View solution in original post

rpiboy
Frequent Visitor

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

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

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors