- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ReportingHelpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
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.
Subject | Author | Posted | |
---|---|---|---|
08-14-2023 06:37 AM | |||
03-12-2024 11:57 PM | |||
09-07-2023 10:06 AM | |||
06-21-2023 04:39 AM | |||
07-02-2024 08:09 AM |
User | Count |
---|---|
17 | |
14 | |
13 | |
9 | |
8 |