Convert Month text to Date

[StephenGW]

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

[v-shex-msft]

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

[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]

@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

[v-shex-msft]

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

[StephenGW]

@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.

[v-shex-msft]

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

[StephenGW]

@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,

[v-shex-msft]

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

[StephenGW]

@v-shex-msft 

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

[HotChilli]

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)

[StephenGW]

@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