Reply
chadi_h39
New Member
Partially syndicated - Outbound

Cannot convert a value of type Record to type Table

Hello, 

 

I keep getting the following error:
Expression.Error: We cannot convert a value of type Record to type Table.
Details:
Value=
$numberDouble=NaN
Type=[Type]

Here is my code:
let
Source = Odbc.Query("select recipeId, sensoryProfile.Color_Tag, sensoryProfile.Aroma_Tag, sensoryProfile.Texture_Tag, sensoryProfile.Taste_Tag, sensoryProfile.Mouthfeel_Tag
from recipesSensory"),
#"Reordered Columns" = Table.ReorderColumns(Source,{"recipeId", "Aroma_Tag", "Color_Tag"}),
#"Parsed JSON" = Table.TransformColumns(#"Reordered Columns",{{"Color_Tag", Json.Document}, {"Aroma_Tag", Json.Document}, {"Mouthfeel_Tag", Json.Document}, {"Taste_Tag", Json.Document}, {"Texture_Tag", Json.Document}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Parsed JSON", {"recipeId"}, "Attribute", "Value"),
#"Expanded Value" = Table.ExpandListColumn(#"Unpivoted Other Columns", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Value", "Value", "Value - Copy"),
#"Expanded Value2" = Table.ExpandRecordColumn(#"Duplicated Column", "Value", {"Aroma_Tag"}, {"Value.Aroma_Tag"}),
#"Added Custom" = Table.AddColumn(#"Expanded Value2", "Custom", each try [Value.Aroma_Tag] otherwise [#"Value - Copy"]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value.Aroma_Tag", "Value - Copy"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Tag"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","_Tag","",Replacer.ReplaceText,{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Tag] <> null and [Tag] <> ""))
in
#"Filtered Rows"

I have been looking into this the whole day with no luck. I believe the problem is with unpivoting the columns. I desperately need to unpivot them so i cannot ignore this step. Can anybody guide me to the right direction?

1 REPLY 1
edhans
Super User
Super User

Syndicated - Outbound

I am not immediately seeing anything wrong with  your code. I suspect the issue is in the Parsed JSON line and some of your data is returning records and not a table, so the expansion isn't working for those records.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)