cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JustinNgan
Regular Visitor

MID error when using two calculated columns for integer parameters

I would like to use the MID function for a new column. MID (Text, Start Position, Number of Characters)

 

Note, both Position and Number of Characters are calculated columns.

 

When I check this, I receive the error "An argumen of function 'MID' has the wrong data type or has an invalid value."

 

When I replace one of the calculated numbers with a test number, it works.

 

Why does it appear as though I can only use one calculated column for the parameters to my MID function?

 

Error_1.pngError_2.png

 

2 ACCEPTED SOLUTIONS

Whole numbers 😞  but I think you got it ... there are definitely some records where the value of the calculated columns are null/blank.  I guess I can use the function to calculate on only those with valid entries by wrapping an outer function to test for a number?

View solution in original post

I just confirmed that a blank being returned in a calculated column value will cause the error you posted. You can get around that by doing something like this:

 

USERNAME = MID([LOG_MESSAGE], if(isblank([USERNAME_START]), 1, [USERNAME_START]), if(isblank([USERNAME_LENGTH]), 1, [USERNAME_LENGTH]))

 

Not knowing what your data looks like, I just used the isblank to check to see if there is a blank value in the calculated column and then replaced the blank with a 1. Same with the length in the second parameter. You may have to pick other values, but that should prevent blanks from breaking your formula.

View solution in original post

5 REPLIES 5
JustinNgan
Regular Visitor

One additional point, as I continue to try and figure this out, it appears that it is using a Column as the LENGTH parameter that is causing the error with the mid FUNCTION.

 

Can anyone else try to use a MID function on their data and see if this is indeed a case / problem?

I just tried using calculated columns as both the start position and the length and it worked.

 

What are the data types of the columns you are using? Do you have sample data? Are the columns you are using for the start position and length ever null, blank, or text?

Whole numbers 😞  but I think you got it ... there are definitely some records where the value of the calculated columns are null/blank.  I guess I can use the function to calculate on only those with valid entries by wrapping an outer function to test for a number?

View solution in original post

I just confirmed that a blank being returned in a calculated column value will cause the error you posted. You can get around that by doing something like this:

 

USERNAME = MID([LOG_MESSAGE], if(isblank([USERNAME_START]), 1, [USERNAME_START]), if(isblank([USERNAME_LENGTH]), 1, [USERNAME_LENGTH]))

 

Not knowing what your data looks like, I just used the isblank to check to see if there is a blank value in the calculated column and then replaced the blank with a 1. Same with the length in the second parameter. You may have to pick other values, but that should prevent blanks from breaking your formula.

View solution in original post

THANK YOU TROTZUK, 

 

Some extra help finding the cause of errors from smart folks like yourselves makes the experience much better!  

 

Again, thanks for your help!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.