Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
As seen in screenshot below, I have a column with month names in text format. When I try to transform to date it gives me errors all across. I tried to do "parse" and it doesnt pick up on the month names for some reason, still giving errors. What should I do to change to a date/month format so I can rank my sales data by month in reports?
Also, if Power BI cant tell if June is a month or not, what's the function of "parse"?
Thank you,
Solved! Go to Solution.
Hi @Alchemista ,
As alexvc suggested, you could create the custom column to achieve your requirement.
Please refer to this M query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTIyMLRQitWJVnJLTSpCF/NNLErOQBZwLCjKzEFVgaLBqzQvFc6PBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Year", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Month] = "January" then 1 else if [Month] = "February" then 2 else if [Month] = "March" then 3 else if [Month] = "April" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else null), #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Year", type text}, {"Custom", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Year]&"-"&[Custom]&"-"&"1"), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom.1", "date"}}), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"date", type date}}) in #"Changed Type2"
Here is the output.
You also could refer to my attachement.
Best Regards,
Cherry
Hi @Alchemista ,
As alexvc suggested, you could create the custom column to achieve your requirement.
Please refer to this M query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlTSUTIyMLRQitWJVnJLTSpCF/NNLErOQBZwLCjKzEFVgaLBqzQvFc6PBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Year", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Month] = "January" then 1 else if [Month] = "February" then 2 else if [Month] = "March" then 3 else if [Month] = "April" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else null), #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Year", type text}, {"Custom", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Year]&"-"&[Custom]&"-"&"1"), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom.1", "date"}}), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"date", type date}}) in #"Changed Type2"
Here is the output.
You also could refer to my attachement.
Best Regards,
Cherry
Hi there,
Date format conversion parting from text can be tricky. A quick fix can be to create a calculated column in your table with an IF(Period="January",1,IF(Period="February",2,.... formula to assign the month number to each record. In the sample data you posted that column should show "6" in all rows, as that's June's month number. Then add a separate column to calculate the date using the formula DATE(day,month,year). If you don't have a day column in your table input 1 and that will give you a date format for the 1st of the month
Let me know if it works
Alejandro
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |