Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cado_one
Resolver III
Resolver III

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
Anonymous
Not applicable

try using this form, without type number

 

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

 

 

image.png

View solution in original post

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
Anonymous
Not applicable

try using this form, without type number

 

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

 

 

image.png

@Anonymous 

 

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

Anonymous
Not applicable

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

 

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"

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

@Anonymous @Smauro 

 

Thank you very much for your help, I deleted each step until I was error free and the problem was indeed caused by the null values unaccepted by the Text.contains in [MAINTENANCETYPE] !

 

There's no more troubles now.

 

Thank you again,

Cado

Anonymous
Not applicable

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.

 

 

Hi @Anonymous 

 

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.

Anonymous
Not applicable

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,

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors