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

     

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.