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

[arjun_para]

 

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

[danextian · Accepted solution]

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.