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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
StephenGW
Helper II
Helper II

Convert 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 11
v-shex-msft
Community Support
Community Support

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.

Custom Sorting in Power BI 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
HotChilli
Super User
Super User

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/ 

@HotChilli 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 

 

Thank you. I was unable to find a way to do this so I went about it the difficult way. I resturctured my Power App to use dates rather than just the text of the month name and then went back and changed all the old data into dates as well.

 

Thank you for your time and help with this issues.

Hi @StephenGW,

You mentioned you are working with 'live connection' to these files. What type of data connector are you worked? How did you link to these files? Can you please share more detail about these?

AFAIK, power bi also allows you to use a web connector to link to the file stored in onedrive or sharepoint. It will keep edit features and allow you to do more data model operations.

Use OneDrive for Business links in Power BI Desktop - Power BI | Microsoft Docs
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 

 

That is a great link. I did not know about this option. I linked it through Get Data>Files>OneDrive for Business>Import. My question with the method mentioned in the link, how does that work with sharing? In order to share with everyone in my organization I had to put both the report and dataset in the preimum workspace as some users are free. Since the data will remain in my OneDrive and not in the premium workspace will it still allow me to share?

 

Thanks,

HI @StephenGW,

In fact, this mode does not affect the share feature usage.
Your report contents will be updated based on your credentials and work well with the users you shared. (Notice: don't forget to verify the data set credentials on the power bi side to enable the auto-refresh for these contents)

Data refresh in Power BI - Power BI | Microsoft Docs
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 

 

Thank you for the great help and information! This is a great community of helpful people.

HotChilli
Super User
Super User

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)

@HotChilli 

 

Yes 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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.