cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

expression.error the type of the value does not match the type of the column

Hi,

 

I got an issue that looks simple to solve but I don't find the trick and I don't find similar topics online.

Let's explain : I made a customizable column to get the difference between to datetime columns. Here is the code :

#"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié2", "Temps d'arrêt", each [WOD2] - [WOD1]),
#"Total calculé des heures" = Table.TransformColumns(#"Personnalisée ajoutée",{{"Temps d'arrêt", Duration.TotalHours, type number}})

WOD1 and WOD2 are the two datetime columns.

The customizable column seems to work but when refresshing data in Power BI I got some errors and when opening the Query editor to see what happend the whole colum got the error : Expression.Error : The type of the value does not match the type of the column.

I tried to change the column type without success.

 

If anyone knows the error or has an idea to fix it I'd be very glad to hear it.

Thanks in advance,

Cado

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: expression.error the type of the value does not match the type of the column

try using this form, without type number

 

= Table.TransformColumns(#"Added Custom" ,{"diff", Duration.TotalMinutes})

 

 

image.png

View solution in original post

Highlighted
Solution Supplier
Solution Supplier

Re: expression.error the type of the value does not match the type of the column

Using Text.Contains on a nullable value results in your issue. Replace nulls in [MAINTENANCETYPE] before that step, or first check for null.




Feel free to connect with me:
LinkedIn

View solution in original post

10 REPLIES 10
Highlighted
Solution Sage
Solution Sage

Re: expression.error the type of the value does not match the type of the column

try using this form, without type number

 

= Table.TransformColumns(#"Added Custom" ,{"diff", Duration.TotalMinutes})

 

 

image.png

View solution in original post

Highlighted
Helper II
Helper II

Re: expression.error the type of the value does not match the type of the column

@Rocco_sprmnt21 

 

I tried and it now returns me on the entire table Expression.Error : Sorry we couldn't convert null into logical.

Highlighted
Solution Sage
Solution Sage

Re: expression.error the type of the value does not match the type of the column

The problem is probably not on this expression.
to analyze better, could you show us the code and also a small part of the table you use?
only a few lines may be sufficient

 

Highlighted
Helper II
Helper II

Re: expression.error the type of the value does not match the type of the column

WOD columnsWOD columnsCustomizable columnCustomizable columnError when refreshing dataError when refreshing data

The first screenshot shows some lines of the WOD columns, the second screen shows the customizable column and the last one is what i get when clicking on the error after refresh and opening the query editor. 

Highlighted
Helper II
Helper II

Re: expression.error the type of the value does not match the type of the column

And this is the full code for this table. Thank you very much for the time you take to help me.

 

let
Source = Excel.Workbook(File.Contents("\\files\Exploitation\Exploitation général\10 - GMAO\00 - KPI\Fichier_import_donnees\Maintenance\WORKORDER.xlsx"), null, true),
TBL_WORKORDER_Sheet = Source{[Item="Données",Kind="Sheet"]}[Data],
#"En-têtes promus" = Table.PromoteHeaders(TBL_WORKORDER_Sheet, [PromoteAllScalars=true]),
#"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"CODE_WORKORDER", type text}, {"DESCRIPTION", type text}, {"ESTSTARTDATE", type datetime}, {"ESTENDDATE", type datetime}, {"REALSTARTDATE", type datetime}, {"REALENDDATE", type datetime}, {"FAILUREDATE", type any}, {"PRIORITY", type any}, {"WORKORDERTYPE", type text}, {"MAINTENANCETYPE", type text}, {"ESTDURATION", type number}, {"STOPTIME", type number}, {"STOPNEEDED", type logical}, {"ISROUTESTOP", type any}, {"STOPNUMBER", type any}, {"STATUS", type text}, {"STATUSDATE", type datetime}, {"CREATEDBY", type text}, {"CREATIONDATE", type datetime}, {"MODIFIEDBY", type text}, {"MODIFICATIONDATE", type datetime}, {"HISTORY", type logical}, {"LABORESTIMATEDCOST", Int64.Type}, {"LABORREALCOST", Int64.Type}, {"ITEMESTIMATEDCOST", type number}, {"ITEMREALCOST", type number}, {"SPREADSTATUS", type logical}, {"WOD1", type datetime}, {"WOD2", type datetime}, {"WOT1", type text}, {"WOT2", type text}, {"WOT3", type text}, {"WOT4", type text}, {"WOT5", type text}, {"WOT6", type text}, {"WOT7", type text}, {"WOT8", type text}, {"WOT9", type any}, {"WOT10", type any}, {"WON1", Int64.Type}, {"WON2", Int64.Type}, {"WON3", type any}, {"WON4", type any}, {"FK_CODE_LAW", type any}, {"FK_CODE_LOCATION", type text}, {"FK_CODE_EQUIPMENT", type text}, {"FK_CODE_PMSHEET", type any}, {"FK_CODE_PLAN", type text}, {"FK_CODE_ROUTE", type any}, {"FK_CODE_LABOR_INCHARGE", type text}, {"FK_CODE_LABOR_REPORTEDBY", type text}, {"FK_CODE_GLACCOUNT", type any}, {"FK_CODE_COMPANY", type text}, {"FK_CODE_CONTRACT", type any}, {"FK_CODE_WORKREQUEST", type any}, {"FK_CODE_WORKORDER", type text}, {"FK_CODE_SITE", type text}, {"FK_CODE_ORGANISATION", type text}, {"FK_CODE_WORKORDERREPORT", type any}, {"FK_CODE_LONGDESCRIPTION", type any}, {"TEXTREPORT", type text}, {"MOBSTATUS", type any}, {"TARGSTARTDATE", type datetime}, {"TARGENDDATE", type datetime}, {"MOBSTATUSDATE", type any}, {"MOBCHANGEDBY", type any}, {"WOD3", type any}, {"WOD4", type any}, {"WOD5", type any}, {"WOD6", type any}, {"WOD7", type any}, {"WOD8", type any}, {"WOD9", type any}, {"FK_CODE_COMPANY_CUSTOMER", type any}, {"WOD10", type any}, {"FK_CODE_CONTRACT_CUSTOMER", type any}, {"WON5", Int64.Type}, {"WON6", Int64.Type}, {"WON7", Int64.Type}, {"WON8", type any}, {"WON9", type any}, {"WON10", type any}, {"WOB1", type logical}, {"WOB2", type logical}, {"WOB3", type logical}, {"WOB4", type logical}, {"WOB5", type logical}, {"WOB6", type logical}, {"WOB7", type logical}, {"WOB8", type logical}, {"WOB9", type logical}, {"WOB10", type logical}, {"WOM1", type text}, {"WOM2", type text}, {"WOM3", type any}, {"WOM4", type any}, {"WOM5", type any}, {"WOT11", type any}, {"WOT12", type any}, {"WOT13", type any}, {"WOT14", type any}, {"WOT15", type any}, {"SCHLABORHRS", type number}, {"FK_CODE_GLACCOUNTSEG_1", type text}, {"FK_CODE_GLACCOUNTSEG_2", type text}, {"FK_CODE_QUOTATION", type any}, {"FK_CODE_CUSTOMERORDER", type any}, {"FK_CODE_RISKASSESS", type any}, {"REPORTDATE", type datetime}, {"RISKASSESSLINENUM", type any}, {"FK_CODE_ANOMALYREPORT", type any}, {"ADDRESS1", type any}, {"ADDRESS2", type any}, {"EEDOMAIN", type any}, {"POSTALCODE", type any}, {"TOWN", type text}, {"EESUBDOMAIN", type any}, {"COUNTRY", type text}, {"EESCORINGCOND1", type any}, {"LONGITUDE", type text}, {"EESCORINGCOND2", type any}, {"LATITUDE", type text}, {"EESCORINGCOND3", type any}, {"EEREALSEVERITY", type any}, {"EEPOTENTIALSEVERITY", type any}, {"EEREALSCORING", type any}, {"EEPOTENTIALSCORING", type any}, {"REALLABHRS", type number}, {"REMAINSCHEDHRS", type number}, {"MAINMETERVALUE", type any}, {"PMSHEETCYCLESEQ", type any}, {"CONFORMITYSTATE", type any}, {"EEEVAL", type any}, {"APTDESCRIPTION", type any}, {"APTPHONENUMBER", type any}, {"APTDESCRIPTIONCOMP", type any}, {"APTPHONENUMBERCOMP", type any}, {"PROGRESSSTATE", type text}, {"CAUSECODE", type any}, {"FAILURE_CODE", type any}, {"REMEDYCODE", type any}, {"DATALOCALE", type text}}),
#"Lignes filtrées" = Table.SelectRows(#"Type modifié", each not Text.Contains([CODE_WORKORDER], "ACT")),
#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Lignes filtrées", "MaintenanceCORR/PREV", each if Text.Contains([MAINTENANCETYPE], "PREV") then "PREV" else if Text.Contains([MAINTENANCETYPE], "RCPT") then "RCPT" else if Text.Contains([MAINTENANCETYPE], "CORR") then "CORR" else if Text.Contains([MAINTENANCETYPE], "ACCO") then "ACCO" else "AUTRE"),
#"Colonne conditionnelle ajoutée1" = Table.AddColumn(#"Colonne conditionnelle ajoutée", "Famille d'équipement", each if Text.Contains([MAINTENANCETYPE], "OND") then "ONDULEUR" else if Text.Contains([MAINTENANCETYPE], "PDL") then "PDL" else if [MAINTENANCETYPE] = "PREV-BJ" then "BJ" else if [MAINTENANCETYPE] = "PREV-HT" then "PDL" else if [MAINTENANCETYPE] = "PREV-PAL" then "EOL" else if [MAINTENANCETYPE] = "PREV-PRO" then "PDL" else if [MAINTENANCETYPE] = "REENCL" then "SUPERVISION" else [WOT6]),
#"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Colonne conditionnelle ajoutée1", {"CODE_WORKORDER", "DESCRIPTION", "ESTSTARTDATE", "ESTENDDATE", "REALSTARTDATE", "REALENDDATE", "FAILUREDATE", "PRIORITY", "WORKORDERTYPE", "MAINTENANCETYPE", "ESTDURATION", "STOPTIME", "STOPNEEDED", "ISROUTESTOP", "STOPNUMBER", "STATUS", "STATUSDATE", "CREATEDBY", "CREATIONDATE", "MODIFIEDBY", "MODIFICATIONDATE", "HISTORY", "LABORESTIMATEDCOST", "LABORREALCOST", "ITEMESTIMATEDCOST", "ITEMREALCOST", "SPREADSTATUS", "WOD1", "WOD2", "WOT1", "WOT5", "WOT6", "WOT7", "WOT8", "WOT9", "WOT10", "WON1", "WON2", "WON3", "WON4", "FK_CODE_LAW", "FK_CODE_LOCATION", "FK_CODE_EQUIPMENT", "FK_CODE_PMSHEET", "FK_CODE_PLAN", "FK_CODE_ROUTE", "FK_CODE_LABOR_REPORTEDBY", "FK_CODE_GLACCOUNT", "FK_CODE_COMPANY", "FK_CODE_CONTRACT", "FK_CODE_WORKREQUEST", "FK_CODE_WORKORDER", "FK_CODE_SITE", "FK_CODE_ORGANISATION", "FK_CODE_WORKORDERREPORT", "FK_CODE_LONGDESCRIPTION", "TEXTREPORT", "MOBSTATUS", "TARGSTARTDATE", "TARGENDDATE", "MOBSTATUSDATE", "MOBCHANGEDBY", "WOD3", "WOD4", "WOD5", "WOD6", "WOD7", "WOD8", "WOD9", "FK_CODE_COMPANY_CUSTOMER", "WOD10", "FK_CODE_CONTRACT_CUSTOMER", "WON5", "WON6", "WON7", "WON8", "WON9", "WON10", "WOB1", "WOB2", "WOB3", "WOB4", "WOB5", "WOB6", "WOB7", "WOB8", "WOB9", "WOB10", "WOM1", "WOM2", "WOM3", "WOM4", "WOM5", "WOT11", "WOT12", "WOT13", "WOT14", "WOT15", "SCHLABORHRS", "FK_CODE_GLACCOUNTSEG_1", "FK_CODE_GLACCOUNTSEG_2", "FK_CODE_QUOTATION", "FK_CODE_CUSTOMERORDER", "FK_CODE_RISKASSESS", "REPORTDATE", "RISKASSESSLINENUM", "FK_CODE_ANOMALYREPORT", "ADDRESS1", "ADDRESS2", "EEDOMAIN", "POSTALCODE", "TOWN", "EESUBDOMAIN", "COUNTRY", "EESCORINGCOND1", "LONGITUDE", "EESCORINGCOND2", "LATITUDE", "EESCORINGCOND3", "EEREALSEVERITY", "EEPOTENTIALSEVERITY", "EEREALSCORING", "EEPOTENTIALSCORING", "REALLABHRS", "REMAINSCHEDHRS", "MAINMETERVALUE", "PMSHEETCYCLESEQ", "CONFORMITYSTATE", "EEEVAL", "APTDESCRIPTION", "APTPHONENUMBER", "APTDESCRIPTIONCOMP", "APTPHONENUMBERCOMP", "PROGRESSSTATE", "CAUSECODE", "FAILURE_CODE", "REMEDYCODE", "DATALOCALE", "MaintenanceCORR/PREV", "Famille d'équipement"}, "Attribut", "Valeur"),
#"Colonnes renommées" = Table.RenameColumns(#"Tableau croisé dynamique des colonnes supprimé",{{"Attribut", "Opérateur 1234"}, {"Valeur", "Nom opérateur"}}),
#"Colonne conditionnelle ajoutée2" = Table.AddColumn(#"Colonnes renommées", "Temps d'inter", each if [Opérateur 1234] = "FK_CODE_LABOR_INCHARGE" then [WON4] else if [Opérateur 1234] = "WOT2" then [WON8] else if [Opérateur 1234] = "WOT3" then [WON10] else if [Opérateur 1234] = "WOT4" then [STOPTIME] else null),
#"Type modifié1" = Table.TransformColumnTypes(#"Colonne conditionnelle ajoutée2",{{"Temps d'inter", type number}}),
#"Colonne conditionnelle ajoutée3" = Table.AddColumn(#"Type modifié1", "Temps de trajet", each if [Opérateur 1234] = "WOT4" then [REALLABHRS] else if [Opérateur 1234] = "WOT2" then [WON9] else if [Opérateur 1234] = "WOT3" then [MAINMETERVALUE] else if [Opérateur 1234] = "FK_CODE_LABOR_INCHARGE" then [WON3] else null),
#"Type modifié2" = Table.TransformColumnTypes(#"Colonne conditionnelle ajoutée3",{{"Temps de trajet", type number}}),
#"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié2", "Temps d'arrêt", each [WOD2] - [WOD1]),
#"Total calculé des heures" = Table.TransformColumns(#"Personnalisée ajoutée",{{"Temps d'arrêt", Duration.TotalHours, type number}})
in
#"Total calculé des heures"

Highlighted
Solution Sage
Solution Sage

Re: expression.error the type of the value does not match the type of the column

I as firts guest, would delete the last step:

 

#"Total calculé des heures" = Table.TransformColumns(#"Personnalisée ajoutée",{{"Temps d'arrêt", Duration.TotalHours, type number}})

and see if you get some error/problem after refresh.

 

 

Highlighted
Helper II
Helper II

Re: expression.error the type of the value does not match the type of the column

Hi @Rocco_sprmnt21 

 

When removing this step, I got the whole table in error "Sorry, we can not convert null value into logical".

Maybe it has an importance, I forget to mention that when refreshing, it says that the error concerns 24 lines out of more than 5600 lines. However, I checked quickly the lines of WOD columns and saw nothing anormal... Moreover the column was working perfectly until a few days ago and I can't say what has changed except the fact that the database format changed but I adapted the query and everything looks fine, apart from this "Temps d'arrêt" column.

Highlighted
Solution Sage
Solution Sage

Re: expression.error the type of the value does not match the type of the column

Let we try to analyze in this way:

 

create a dupliate of your query, so that we can experiment on a copy and your original table is safe 😁.

 

image.png

 

 

Then, after selected the copy on the left side, you should on the other side  delete the last stepS, until you have the first last 😉 step error free.

 

you should do this clicking the X on the left pf the current last step ... 

 

image.png

 

Then we will have more possibilities to examine the last data and the first expression that give problems,

 

Highlighted
Solution Supplier
Solution Supplier

Re: expression.error the type of the value does not match the type of the column

Using Text.Contains on a nullable value results in your issue. Replace nulls in [MAINTENANCETYPE] before that step, or first check for null.




Feel free to connect with me:
LinkedIn

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors