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
all,
How can I extract out from a date field the month and year arguments and create a new coloum with the format:
mm\yyyyy
I have an original date column ("minnipuk") holding the data in a dd/mm/yyyy format.
29/09/2015 |
what I need is - 9-2015 or even better sep-2015.
I did a new measure: in the query editor
Text.From(Date.Month([minnpk]))
&"-"
&Text.From(Date.Year([minnpk]))
but when I used it in an x axis inside a graph the data is not sorted correctly (9-2016 comes after 10-2016 ), and I think I understand why:It's a text not a date.
Is it possible to make the new column I have created a date type?
Should It be done in the query editor or inside the report itself?
Thanks
Erez
Solved! Go to Solution.
Do you have dedicated continuous calendar table in your data model which is best practice for time intelligence based reporting. If you dont have this table, you can create one, here is the link
Although see below on how to extract Month and Year and you need to sort this column by other column to get month order correctly.
Column 2 = FORMAT('Table'[Date], "MM/YY")
Column = FORMAT('Table'[Date], "MMM/YY")
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
You say you also want a column to be "what I need is - 9-2015 or even better sep-2015."
Add a column to your DATE table
Month = FORMAT('Dates'[Date],"mmm-YYYY")
To get this sorted correctly also add the following column
MonthID = INT(format('Dates'[date],"YYYYMM")
Once you have these two columns, Set your Month column to be sorted by your MonthID column.
Thanks, parry2k.
I used your second soultion and it works fine.
I'm not quiet sure I have understood your first solution. I did try to creatie this and got a message "The syntax for ';' is incorrect" I'm not sure why it occurs.
However, suppose I have managed to create the table and also have a 2'nd table with my impoted data that contains a date field,
Do you mean there should be some kind of join between them by the date fields (formated : dd/mm/yyyy) and than get the corresponding date format I'm intersted in ("mm/yyyy") from the date table.
Thanks again
I have question, I need the date field show only data that not blank.
Hello! This is actually my first post being a new user in power query.
I have a text date in "MMM" format. How can I convert it to the current date?
Thnak you in advance.
I need to convert 0h 00m to hours. I haven't been able to find a solution and saw this post about converting text to date. The text is different in each row. Any suggestions would be helpful.
Hi @dchapman
This might work
HOUR( TIMEVALUE( SUBSTITUTE( SUBSTITUTE("0h 00m","h",":") , "m","") ) )
Hi Phil,
Thank you for the formula. I tried adding a custom column using that formula but I may have done it incorrectly. Where do I put in a reference to the original column data? Or should I be doing something different?
This is what I have in my function bar:
= Table.AddColumn(#"Filtered Rows4", "Custom", each HOUR( TIMEVALUE( SUBSTITUTE( SUBSTITUTE("0h 00m","h",":") , "m","") ) ))
I'm really new at using Power Query and not finding a lot of helpful information from Microsoft on how to use this product.
Thanks,
Derik
This code will work as a calculated column in Power BI Desktop (not Power Query)
New Column =
HOUR( TIMEVALUE( SUBSTITUTE( SUBSTITUTE([your column here],"h",":") , "m","") ) )
You say you also want a column to be "what I need is - 9-2015 or even better sep-2015."
Add a column to your DATE table
Month = FORMAT('Dates'[Date],"mmm-YYYY")
To get this sorted correctly also add the following column
MonthID = INT(format('Dates'[date],"YYYYMM")
Once you have these two columns, Set your Month column to be sorted by your MonthID column.
Do you have dedicated continuous calendar table in your data model which is best practice for time intelligence based reporting. If you dont have this table, you can create one, here is the link
Although see below on how to extract Month and Year and you need to sort this column by other column to get month order correctly.
Column 2 = FORMAT('Table'[Date], "MM/YY")
Column = FORMAT('Table'[Date], "MMM/YY")
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks, parry2k.
I used your second soultion and it works fine.
I'm not quiet sure I have understood your first solution. I did try to creatie this and got a message "The syntax for ';' is incorrect" I'm not sure why it occurs.
However, suppose I have managed to create the table and also have a 2'nd table with my impoted data that contains a date field,
Do you mean there should be some kind of join between them by the date fields (formated : dd/mm/yyyy) and than get the corresponding date format I'm intersted in ("mm/yyyy") from the date table.
Thanks again
"I'm not quiet sure I have understood your first solution. I did try to creatie this and got a message "The syntax for ';' is incorrect" I'm not sure why it occurs."
Try changing the ';' to ','
(Change the semicolon to a comma in all places)
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 |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |