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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
arjun_para
Regular Visitor

Powerbi desktop: Imported data from excel formatted wrong How to change month name to month and year

arjun_para_0-1741863201945.png 

I imported this data from Excel to powerbi. Now I need to change column 3 :- (Month name to month and year).

For example From  April to April 2019. My column 3 need to read as April 2019, May 2019.... till Oct 2022

 

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Hi @arjun_para 

 

Assuming that Jan to Mar are from the next calendar year, try this M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MjAyUNJRCjQEEo4FRZk5SrE60UpADhD5JlYi8bxK81JRuDnIso6l6Ui84NSCEhg30AgkhpDzTy5B4vnllyHxXFKT4dqMUbV5JeYh8dxSk1AcWgTmgTwD8pHhcPKRIchHRgPgo1gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1", "Column2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1", "Column2"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column3] <> null and [Column3] <> ""),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Parsed Date", each let 
    // Extracts the first 4 characters from Column1, representing the starting year
    yr1 = Text.Start([Column1],4),  
    
    // Extracts the last 4 characters from Column1, representing the ending year
    yr2 = Text.End([Column1],4),  
    
    // Stores the month name from Column3
    _month = [Column3],  
    
    // Determines which year to use based on the month
    // If the month is Jan, Feb, or Mar, use the second year (yr2)
    // Otherwise, use the first year (yr1)
    year_to_use = 
        if _month = "Jan" or _month = "Feb" or _month = "Mar" 
        then yr2 
        else yr1,  
    
    // Concatenates the first three letters of the month with the selected year
    mo_yr = Text.Start(_month,3) & " " & year_to_use,  
    
    // Converts the concatenated text into a date format
    parsed_date = Date.From(mo_yr)  

in 
    parsed_date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month and Year", each Date.ToText([Parsed Date], "MMMM yyyy")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Parsed Date", type date}, {"Month and Year", type text}})
in
    #"Changed Type1"

Create a blank query, delete everything inside and paste this code to see how the data is being transformed. The result should be as below.

danextian_0-1741873015119.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

1 REPLY 1
danextian
Super User
Super User

Hi @arjun_para 

 

Assuming that Jan to Mar are from the next calendar year, try this M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MjAyUNJRCjQEEo4FRZk5SrE60UpADhD5JlYi8bxK81JRuDnIso6l6Ui84NSCEhg30AgkhpDzTy5B4vnllyHxXFKT4dqMUbV5JeYh8dxSk1AcWgTmgTwD8pHhcPKRIchHRgPgo1gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1", "Column2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1", "Column2"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column3] <> null and [Column3] <> ""),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Parsed Date", each let 
    // Extracts the first 4 characters from Column1, representing the starting year
    yr1 = Text.Start([Column1],4),  
    
    // Extracts the last 4 characters from Column1, representing the ending year
    yr2 = Text.End([Column1],4),  
    
    // Stores the month name from Column3
    _month = [Column3],  
    
    // Determines which year to use based on the month
    // If the month is Jan, Feb, or Mar, use the second year (yr2)
    // Otherwise, use the first year (yr1)
    year_to_use = 
        if _month = "Jan" or _month = "Feb" or _month = "Mar" 
        then yr2 
        else yr1,  
    
    // Concatenates the first three letters of the month with the selected year
    mo_yr = Text.Start(_month,3) & " " & year_to_use,  
    
    // Converts the concatenated text into a date format
    parsed_date = Date.From(mo_yr)  

in 
    parsed_date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month and Year", each Date.ToText([Parsed Date], "MMMM yyyy")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Parsed Date", type date}, {"Month and Year", type text}})
in
    #"Changed Type1"

Create a blank query, delete everything inside and paste this code to see how the data is being transformed. The result should be as below.

danextian_0-1741873015119.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.