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
snocaps248
New Member

New column that replaces text from existing column with new values

I am importing data from SQL and have unpivoted some columns. The columns unpivoted were text columns that referenced the period by month. However the text used is in a rediculous format of FY_01, FY_02, etc. I want these displayed in visuals as month names, January, February, etc... I assume I need to make a new column that references the existing column and changes the text to the proper format. How do I do this? Or is their another way that is more logical?

 

snocaps248_0-1598533106762.png

 

Thanks

1 ACCEPTED SOLUTION

HI @snocaps248 ,

 

You can create a DUPLICATE column of your existing column and then follow the steps that I have suggested.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

4 REPLIES 4
Pragati11
Super User
Super User

Hi @snocaps248 ,

 

Try following steps:

Consider following column:

Pragati11_0-1598533510994.png

 

Go to Query Editor and extract after delimiter:

Pragati11_1-1598533590812.png

 

Pragati11_2-1598533616185.png

 You will get following:

Pragati11_3-1598533646606.png

 

Modify column's data-type to WHOLE NUMBER:

Pragati11_4-1598533676895.png

 

Apply query settings.

Just create a new DAX column as - 

Month Name = FORMAT(DATE(1, 'Month test'[Col1], 1), "MMMM")
 
1f.png

You get the required result.

 

Thanks,

Pragati

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

If I make the changes to the existing column as you suggest, will that break the refresh of data? I have set this dataset to refresh from our on-prem sql nightly for it to update the dashboard for morning execs meeting.

 

Thanks,

 

HI @snocaps248 ,

 

You can create a DUPLICATE column of your existing column and then follow the steps that I have suggested.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Greg_Deckler
Super User
Super User

@snocaps248 - Well, you could write a nested if then else statement in Power Query or you could use a SWITCH statement in DAX like:

 

Month = 
  SWITCH([Period],
    "FY_01","January",
    "FY_02","February",
    ...
  )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.