cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors