Forum Discussion

stefans124's avatar
stefans124
New Member
4 years ago
Solved

Month id to month name using power query

Hello there, im having a little challenge with my dash board. 

 

Here i have a slicer, that is connected to all other tables and makes them change information based on the month selected. So i have an issue with changing these 20221, 20222 etc. to their month names  (respectively, 20221 needs to be january, 20222 needs to be february etc.) I wanted to ask for help on how to do it, so that my slicer shows the names of the month not these numbers, and im skeptical that if i change it based on "how it think it should be done" then the relationships between tables might vanish.

I appreaciate your time for reading this and any help or solution will be appreciated 🙂

 

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Hi stefans124,

    Have a look at the following code

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjJUitWBsIzgLGM4ywTOMoWzzOAsczjLAs6yhLMMDRBMhC2GQGtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id_menesis = _t]),
    AddMonthName = Table.AddColumn(Source, "MonthName", each Date.ToText(Date.FromText([id_menesis],[Format="yyyyM"]),"MMMM"),type text),
    AddMonthNum = Table.AddColumn(AddMonthName, "MonthNum", each Int64.From(Text.Middle([id_menesis], 4,2)), Int64.Type)
in
    AddMonthNum

     


    You will need the MonthNum integer column to sort the month names. See this link



    Showcase Report – Contoso By SpartaBI


          

2 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Hi stefans124,

    Have a look at the following code

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjJUitWBsIzgLGM4ywTOMoWzzOAsczjLAs6yhLMMDRBMhC2GQGtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id_menesis = _t]),
    AddMonthName = Table.AddColumn(Source, "MonthName", each Date.ToText(Date.FromText([id_menesis],[Format="yyyyM"]),"MMMM"),type text),
    AddMonthNum = Table.AddColumn(AddMonthName, "MonthNum", each Int64.From(Text.Middle([id_menesis], 4,2)), Int64.Type)
in
    AddMonthNum

     


    You will need the MonthNum integer column to sort the month names. See this link



    Showcase Report – Contoso By SpartaBI


          

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi stefans124 ,

     

    You may firstly try SpartaBI 'method using M in Power Query.

    Mine is using DAX to create calculated columns:

    Month Number = CONVERT( RIGHT([id_menesis], LEN([id_menesis])-4),INTEGER)
    Month Name = FORMAT( DATE(LEFT([id_menesis],4),[Month Number],1),"mmmm")

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.