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?
