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



    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

    Showcase Report – Contoso By SpartaBI


    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    SpartaBI Logo

2 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity 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



    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.

    Showcase Report – Contoso By SpartaBI


    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    SpartaBI Logo

  • 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.