DAX to move the text value in a column to the right order

[Apple08]

Hi

I noticed some of my Power BI data are corrupted in a column without valid reason.

The correct data should be read as: e.g. Budget|20/21, Plan|20/21, Actual|20/21, Budget|21/22, Plan|21/22, Actual|21/22 and so on,so it should be read as 'Description'|Financial year'.  However I noticed some of the data are put as 'Financial year|Description' such as 21/22|Budget.  Therefore It affects my reporting.

I can't find out what has caused the issue, therefore I plan to use DAX formula to create another column to adjust the order of the description and financial year.  I wonder can anyone help to suggest any dynamic formulars to fix this issue.  The descriptions are the same but financial years are started from 19/20 and on going.  

Any help would be much appreciated.  Thanks.

[AlB · Accepted solution]

Hi @Apple08 

Code for the new, corrected, column:

NewColumn = 
VAR separatorPos_ = FIND("|", Table1[OriginalColumn],1,0)
VAR firstHalf_ = LEFT(Table1[OriginalColumn],separatorPos_ - 1)
VAR isDefective_ = FIND("/", firstHalf_, 1, 0) > 0
RETURN
IF(isDefective_, MID(Table1[OriginalColumn],separatorPos_ + 1,LEN(Table1[OriginalColumn])-separatorPos_) & "|" & firstHalf_, Table1[OriginalColumn]) 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Apple08]

Thank you very much for your help AIB!  You are awesome!

[AlB]

@Apple08 

It looks like it cannot find the "|" in some cases. I mentioned the "/" in the previous post but if the LEFT fails it must be the "|". Is it present in all rows? Anyway, i don't see an issue with using IFERROR, just make sure you are covering all cases, like what happens when there is no "|" in the text. I would need the actual data too look further, but i think you can work it out

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Apple08]

Thanks AIB.  If I have a choice, I would not add the if error into the DAX, however it doesn't work if I don't add if error in, it shows an error message : An argument of function 'LEFT' has the wrong data type or has an invalid value.  

Please let me know if there is a solution to resolve it.

[AlB]

@Apple08 

If you need the iferror, it means there are cases where the "/" cannot be found in the string. Perhaps you need to review if the approach works for those cases, since we were using the the "/" to tell apart the half with the years

Please mark the post as solution when done

Cheers

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Apple08 · Accepted solution]

I have adjusted the DAX to the follows with if error to the 'LEFT' formula.  It looks like it works, please point it out if I have made any mistakes:

TagSpecFY = 
VAR separatorPos_ = FIND("|", summ_task[tags],1,0)
VAR firstHalf_ = iferror(LEFT(summ_task[tags],separatorPos_ - 1)," ")
VAR isDefective_ = FIND("/", firstHalf_, 1, 0) > 0
RETURN
IF(isDefective_, MID(summ_task[tags],separatorPos_ + 1,LEN(summ_task[tags])-separatorPos_) & "|" & firstHalf_, summ_task[tags]) 

[Apple08]

Thanks AIB.

I forgot to mention that some of the data they are in the right order 'Description\Financial Year' but some are not.  I wonder does the DAX above apply to the mix data?

Thanks.

[AlB]

@Apple08

Sure, it takes into account the mixed data. It assumes though that the "/" character only  appears on the "year" part (e.g. 21/22) since we are looking for that character to tell the two halves apart.

See it all at work in the attached file.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

Hi @Apple08 

Code for the new, corrected, column:

NewColumn = 
VAR separatorPos_ = FIND("|", Table1[OriginalColumn],1,0)
VAR firstHalf_ = LEFT(Table1[OriginalColumn],separatorPos_ - 1)
VAR isDefective_ = FIND("/", firstHalf_, 1, 0) > 0
RETURN
IF(isDefective_, MID(Table1[OriginalColumn],separatorPos_ + 1,LEN(Table1[OriginalColumn])-separatorPos_) & "|" & firstHalf_, Table1[OriginalColumn]) 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Apple08]

Thank you.  I got an error message: An argument of function 'LEFT' has the wrong data type or has an invalid value.

The data type of the orginal value is 'text', I am not sure is it

an issue?