Expression.Error: We cannot convert the value 1 to type Text. Details: Value=1 Type=[Type]

[Tanmayjain077]

Hi dear experts,

I am facing the following error while appening the data in power query in excel - 

"Expression.Error: We cannot convert the value 1 to type Text.
Details:
Value=1
Type=[Type]"

The screenshot of the error is as under: -Error Screenshot

The entire code in Append 1 reads as under: -

let
    Source = Table.Combine({CGST_Bran_Input, CGST_Input, IGST_Input}),
    #"Renamed Columns" = Table.RenameColumns(Source,MappingList,MissingField.Ignore),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",Data_Type_master,MissingField.Ignore),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Gross Total", "Quantity", "Narration", "Gstin/Uin", "Voucher Ref. No.", "Voucher No.", "Voucher Type", "Particulars", "Date"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Category Treatment Helper", each List.PositionOf(Mapping_Treatment[Refined name],[Attribute])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Category Treatment", each Mapping_Treatment[Treatment]{[Category Treatment Helper]}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Voucher Type Index", each List.PositionOf(Mapping_Voucher_Type[Voucher Type],[Voucher Type])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Voucher Treatment", each Mapping_Voucher_Type[Vch Treatment]{[Voucher Type Index]})
in
    #"Added Custom3"

The query in Data_type_master reads as under: -

let
    Source = Excel.CurrentWorkbook(){[Name="Data_Type_master"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Value.Type(if [Type] = "date" then #date(2020,1,1) else
if [Type] = "number" then 1 else
"A")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Type"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    Custom1 = Table.ToColumns(#"Transposed Table")
in
    Custom1

Unable to figure out the cause of of the error. Please help.

[v-yalanwu-msft]

Hi, @Tanmayjain077 ;

Is your problem solved?  If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @Tanmayjain077 ;

You could try to change 1 to "1",

Or  removing Rows that are blank. 

Solved: Expression.Error: We cannot convert the value to t... - Microsoft Power BI Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.