Forum Discussion
Helper IIConvert Month text to Date
I have a line chart that goes by months. The problem is the months are full month text January, February, ect. The data is linked to an Excel file in my OneDrive so there are no tables in my Power BI to add a column to convert it to. Also the Excel sheet is fed from a Power App so the table cannot contain any formulae. So my question, is there anyway to convert these months to dates like the first of each month then format it to only show month name that way the chart sorts the data in the correct order?
Thanks,
StephenGW
11 Replies
- HotChilli
Community Champion
Is your question really "how do i make sure my axis displays in month order rather than alphabetical?"?
If so, you can use the 'Sort by column' functionality. You can search the forum for similar or add a column with 1-12 for the month (possibly in a dimension table)
StephenGWYes that is the idea behind it but I don't understand how Sort by column will work if all the columns involved are text. I will search the forum for this and see if I can figure something out. How do I add a column in a measure? I could not figure that out, but I did try to create a measure that would do that with no luck. I will continue to search the forum and the web but I have not had much luck so far.
Thanks
- HotChilli
You're probably overcomplicating it. This bit - "add a column with 1-12" means the new column will be a number not text.
The reason I didn't spell it out further is that there are so many examples of this problem (or similar) out there.
This is one way to do it:
https://www.sqlshack.com/how-to-sort-months-chronologically-in-power-bi/
- StephenGW
I don't have any tables "IN" my BI. They are all a live connection to OneDrive so I can't add a column in BI. The spreadsheets are fed by a Power App so the table cannot contain any formulae. So adding a column is not as simple as I think it should be. I have read many articles like the one you posted but it says to add a calculated column into the table but I have not tables in BI.
Thank you for the suggestion and link but those are not an option. Do you know of any other ways this can be accomplished?
Stephen
Hi StephenGW,
Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi HotChilli,
In fact, if you refer to these steps to get data from files to power bi desktop, the edit features will not be limited to use.
After these steps, you can refer to other community user sharing to create calculated expressions about data type conversions.In addition, if you did not want to change these field types, you can also consider creating a custom sort table for the specific fields sorting.
For the fields text formatting, you can also take a look at the below document about power bi custom string of field values:
Use custom format strings in Power BI Desktop - Power BI | Microsoft Docs
Regards,Xiaoxin Sheng
