Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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"
Solved! Go to Solution.
Hi @hasnain0204
The below line explicitly converts the GRADE and Status columns to text using Table.TransformColumnTypes before performing any operations on them. This prevents errors when applying text functions like Text.Upper and Text.Trim.
// Convert GRADE and Status columns to Text type to prevent errors
#"Converted Columns to Text" = Table.TransformColumnTypes(Source, {{"GRADE", type text}, {"Status", type text}})
Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz
Hi @hasnain0204
The below line explicitly converts the GRADE and Status columns to text using Table.TransformColumnTypes before performing any operations on them. This prevents errors when applying text functions like Text.Upper and Text.Trim.
// Convert GRADE and Status columns to Text type to prevent errors
#"Converted Columns to Text" = Table.TransformColumnTypes(Source, {{"GRADE", type text}, {"Status", type text}})
Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz