Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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) |
2019 | 1 | 201901 |
2019 | 2 | 201902 |
2019 | 3 | 201903 |
2019 | 4 | 201904 |
2019 | 5 | 201905 |
2019 | 6 | 201906 |
2019 | 7 | 201907 |
2019 | 8 | 201908 |
2019 | 9 | 201909 |
2019 | 10 | 201910 |
2019 | 11 | 201911 |
Pedro
Solved! Go to Solution.
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
Proud to be a Super User!
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
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.
Thank you to all of you ! Your answers helped me!
Good weekend.
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
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.
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
Proud to be a Super User!