Forum Discussion
Group values
Sorry Borntoreport,
I skipped your question about the conversion. To solve it you have to:
1) Split the column by number of characters ( Transform -> Split columns -> By number of characters) with:
- "4" as Number of character (to indicate the year)
- "Once, as far left as possible" as Split
2) Re-split the right column by number of characters with:
- 2 as number of character (to separate the month from the day)
- "Once, as far left as possible" as Split
3) After that you have three columns (year-month-day)
4) All you have to do is merge the columns (Add column -> Merge columns) with a custom separator/tab/space (as you want)
5) Finally you select the column and change the data type into date.
I remind to you that I'm not an expert, certainly there is a DAX formula to do it.
Hey B129,
thank you for your fast response! I have tried it already and it works!
Borntoreport, if you just need to point out the business years "2014-2015" and "2015-2016" you can use this solution with M language (that you can adjust and expand for another use with more "business years").
1) "Get data" and modify the data
2) Select the column and let the query editor autodetect the data type for you ( Transform -> Detect Data Type)
3) Your data type is now changed into "Whole number"
4) Now you can add a custom column (Add Column -> Add Custom Column) with this formula:
(. . .)
if [Column1] >= 20151001 then if [Column1] <= 20160930 then "Business Year 2015-2016" else "Other business year" else if [Column1] >= 20141001 then "Business Year 2014-2015" else "Other Business Year"
(. . .)
I'm not an expert but maybe is better use this formula instead of the previous solution that I gave to you.
Let me know if it works.