Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pedro77000
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
Nathaniel_C
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Jimmy801
Community Champion
Community Champion

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
Pedro77000
Frequent Visitor

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

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.

Pedro77000
Frequent Visitor

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

Good weekend.

Jimmy801
Community Champion
Community Champion

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

 

dax
Community Support
Community Support

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.

 

Nathaniel_C
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors