Forum Discussion

Pedro77000's avatar
Pedro77000
Frequent Visitor
6 years ago
Solved

Concatenation, date transformation: YYYYMM

Hello everyone,

I am new to the forum. I have a problem and I need your help.

 

In a table, I have two fields of type (text). My goal is to get whole numbers (as in the goal field) to use them in measures.

 

I managed to insert a column by concatenating text and converting the result to a whole number, but for the months of October, November and December I get 2019010, 2019011 and 2019012 instead of 201910, 201911 and 201912 Which blocks me in the realization of my measurements.

 

I will be grateful if you can help me.

 

Year (text)Month (text)Goal (Whole number)
20191201901
20192201902
20193201903
20194201904
20195201905
20196201906
20197201907
20198201908
20199201909
201910201910
201911201911

 

Pedro

Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi Pedro77000 ,

    Text.Combine({[Year],if Text.Contains([#"Month (text)"],"10") or Text.Contains([#"Month (text)"],"11") or Text.Contains([#"Month (text)"],"12") then "" else "0",[#"Month (text)"] })

    Combines the two columns, but first does a check to see if 10 11  12 are included, if not adds a 0. (I did drop text from your year column.)

     



    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

     

  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Pedro77000 

     

    so a third solution you can choose of.. 🙂

    = Table.AddColumn(#"Changed Type", "Custom", each if Text.Length([#"Month (text)"])=1 then [#"Year (text)"] &"0"&[#"Month (text)"] else [#"Year (text)"]&[#"Month (text)"])

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

     

6 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Pedro77000 

     

    so a third solution you can choose of.. 🙂

    = Table.AddColumn(#"Changed Type", "Custom", each if Text.Length([#"Month (text)"])=1 then [#"Year (text)"] &"0"&[#"Month (text)"] else [#"Year (text)"]&[#"Month (text)"])

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

     

  • Pedro77000's avatar
    Pedro77000
    Frequent Visitor
    6 years ago

    Thank you to all of you ! Your answers helped me!

    Good weekend.

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Pedro77000 ,

    Text.Combine({[Year],if Text.Contains([#"Month (text)"],"10") or Text.Contains([#"Month (text)"],"11") or Text.Contains([#"Month (text)"],"12") then "" else "0",[#"Month (text)"] })

    Combines the two columns, but first does a check to see if 10 11  12 are included, if not adds a 0. (I did drop text from your year column.)

     



    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

     

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Pedro 77000, 

    You could try below M code to see whteher it work ot not

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc+7CcBADIPhXVxfYfuenuXw/mvEgSioEPx8ne4VVwtpYrU31STbrw511g7trAM6WCd0si7oYt3QzXqghzWgwWr6cQUz3lVkPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Year (text)" = _t, #"Month (text)" = _t, #"Goal (Whole number)" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Number.FromText([#"Year (text)"])*100+Number.FromText([#"Month (text)"]))
in
    #"Added Custom"

    Best Regards,
    Zoe Zhi

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

     

  • Pedro77000's avatar
    Pedro77000
    Frequent Visitor
    6 years ago
    Hello everyone,

    I'm having a problem again:

    I want to calculate the amount over time (the current month and the previous months). For that I have to use the formula:

    Calculate (sum (.......); previousmonth (Date table [.....]); all (Fact_Table [...])).

    However, the link between the fact table and the Dimension date table is not date type but type text. This is a type field: Junuary 2019, february 2019 ... etc. This fields I got it with the formula:

    Date.MonthName ([date of last extraction], "en-US") & "" & number.totext (Date.Year ([date of last extraction]))

    My question is: How to transform a text field containing: Junuary 2019, February 2019, March 2019 ..... in Date type with the same format: Junuary 2019, February 2019, March 2019 ??

    I know that in Power Query we can do it, but I do it in French: Janvier 2019, Février 2019 .... etc. Although my Power BI is set to English.
    • dax's avatar
      dax
      Community Support
      6 years ago

      Hi Pedro77000,

      It seems that you want to change Jan 2019 to date type, right? If so, you could try to use change type ->Local to see whether it work or not.

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK8tMLVIwMjC0VIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "name", "name - Copy"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Duplicated Column", {{"name", type date}}, "fr-BJ")
in
    #"Changed Type with Locale"

      Best Regards,
      Zoe Zhi

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