Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
14 | |
13 | |
12 | |
12 | |
12 |