i am working with power Query on desktop and I have facing this
error.Expression.Error: We cannot convert the value 1 to type Text.
Details:
Value=1
Type=[Type]
it is for two specific columns but i changed their data type as Text in excel before refreshing the data in power bi but it still doesnt seem to work
This is the Code:
this is the M language code inside the BI
let
Source = Table.Combine({#"Building_Take-up", #"Building_Office Stock"}),
#"Inserted Uppercased Text" = Table.AddColumn(Source, "UPPERCASE", each Text.Upper([GRADE]), type text),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Uppercased Text",{{"UPPERCASE", "UPPER Status"}}),
#"Added Conditional Column2" = Table.AddColumn(#"Renamed Columns1", "Order_Grade", each if [UPPER Status] = "C" then 5 else if [UPPER Status] = "B" then 3 else if [UPPER Status] = "A-PROJECT" then 1 else if [UPPER Status] = "A" then 2 else if [UPPER Status] = "B/C" then 4 else if [UPPER Status] = "N/A" then 6 else 5, type number),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2",
"Order_Status",
each if Text.Trim([Status]) = "Under construction" then 1
else if Text.Trim([Status]) = "Project with permit" then 2
else if Text.Trim([Status]) = "Project without permit" then 3
else if Text.Trim([Status]) = "Existing Building" then 4
else if Text.Trim([Status]) = "n/a" then 5
else 6,
type number
),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column3",{{"Order_Grade", Int64.Type}, {"Order_Status", Int64.Type}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type"),
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "BuildingKey", 1, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"PROPERTY NAME", "Property Name"}, {"GRADE", "Grade"}, {"UPPER Status", "UPPER Grade"}})
in
#"Renamed Columns"
and this is the code provided by ChatGpt through which the error is solved, however, I didnt completely understand what is the difference. Could someone explain? Thanks.
CODE BY CHATGPT:
let
// Combine Tables
Source = Table.Combine({#"Building_Take-up", #"Building_Office Stock"}),
// Convert GRADE and Status columns to Text type to prevent errors
#"Converted Columns to Text" = Table.TransformColumnTypes(Source, {{"GRADE", type text}, {"Status", type text}}),
// Apply Text.Upper to the GRADE column
#"Inserted Uppercased Text" = Table.AddColumn(#"Converted Columns to Text", "UPPERCASE", each Text.Upper([GRADE]), type text),
// Rename column for readability
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Uppercased Text",{{"UPPERCASE", "UPPER Status"}}),
// Add Order_Grade column with conditional logic
#"Added Conditional Column2" = Table.AddColumn(#"Renamed Columns1", "Order_Grade", each
if [UPPER Status] = "C" then 5
else if [UPPER Status] = "B" then 3
else if [UPPER Status] = "A-PROJECT" then 1
else if [UPPER Status] = "A" then 2
else if [UPPER Status] = "B/C" then 4
else if [UPPER Status] = "N/A" then 6
else 5, type number),
// Add Order_Status column with conditional logic
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2",
"Order_Status", each
if Text.Trim([Status]) = "Under construction" then 1
else if Text.Trim([Status]) = "Project with permit" then 2
else if Text.Trim([Status]) = "Project without permit" then 3
else if Text.Trim([Status]) = "Existing Building" then 4
else if Text.Trim([Status]) = "n/a" then 5
else 6, type number),
// Change types for final columns
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column3",{{"Order_Grade", Int64.Type}, {"Order_Status", Int64.Type}}),
// Remove duplicates
#"Removed Duplicates" = Table.Distinct(#"Changed Type"),
// Add index column for unique key
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "BuildingKey", 1, 1, Int64.Type),
// Final renaming for consistency
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"PROPERTY NAME", "Property Name"}, {"GRADE", "Grade"}, {"UPPER Status", "UPPER Grade"}})
in
#"Renamed Columns"
