Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I am trying to replace a text substring in a column, ideally directly in the Query Editor.
Let's call the column 'YearMonth', fields look like this: "202001", "201912", etc.
I'd like to replace the two last positions of the text strings where "01" should become "Jan", "02" become "Feb", "03" become "Mar" etc. I tried to do so with the "right"-function in combination with an if-statement. PowerBi prompts an error message for the if-statement not being allowed.
Any help would be appreciated. In the meantime I did a manual work-around in the source file, however would like to skip that step for the future.
Solved! Go to Solution.
Hi @1up
try this monster custom column
= Text.Combine({Text.Range([Date],0,4),Text.Range(Date.MonthName(#date(Number.FromText(Text.Range([Date],0,4)),Number.FromText(Text.Range([Date],4,2)),1)),0,3)})
but be sure that [Date] column has a Text format before add new column step
Hi @1up
try this monster custom column
= Text.Combine({Text.Range([Date],0,4),Text.Range(Date.MonthName(#date(Number.FromText(Text.Range([Date],0,4)),Number.FromText(Text.Range([Date],4,2)),1)),0,3)})
but be sure that [Date] column has a Text format before add new column step
Thanks a kudos for the help.
Hi @1up
Create custome column
=Text.Combine({Text.Middle(Text.From([YearMonth]),0,4),Text.Middle(Date.MonthName(#date(2020,Number.FromText(Text.Middle(Text.From([YearMonth]),4,2)),01)),0,3)})
YearMonth column is of Int.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Thank you for the support and quick reply, Pravin.