cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Pedro77000 Frequent Visitor
Frequent Visitor

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: Concatenation, date transformation: YYYYMM

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.)
cust12.PNG

 



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
cust1.PNG

 

View solution in original post

Jimmy801 New Contributor
New Contributor

Re: Concatenation, date transformation: YYYYMM

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

 

View solution in original post

6 REPLIES 6
Nathaniel_C Super Contributor
Super Contributor

Re: Concatenation, date transformation: YYYYMM

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.)
cust12.PNG

 



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
cust1.PNG

 

View solution in original post

Community Support Team
Community Support Team

Re: Concatenation, date transformation: YYYYMM

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.

 

Jimmy801 New Contributor
New Contributor

Re: Concatenation, date transformation: YYYYMM

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

 

View solution in original post

Pedro77000 Frequent Visitor
Frequent Visitor

Re: Concatenation, date transformation: YYYYMM

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

Good weekend.

Pedro77000 Frequent Visitor
Frequent Visitor

Re: Concatenation, date transformation: YYYYMM

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.
Community Support Team
Community Support Team

Re: Concatenation, date transformation: YYYYMM

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)