Power BI Text to Date conversion - Extreme newbie

[lovelylemon]

Hi All,

I have tried my best to figure this out (many hours), read many pages and I still cannot get this right, please could I get some help. I am new to Power BI.

I want to convert the orginal data column "Fiscal Quarter" into a new useable custom colum in a date format in order to analyse the information. Original Data from the organisation is in a Year and Qtr format, i.e. "2024 Q1", ""2024 Q2". Therefore, I am happy for this to be in the format "01/01/2024", "01/04/2024 to represent the start of the QTR. Below is the current snapshot.

Thank you all in advance.

[lovelylemon]

I would just like to say, I solved it using a combination of all the work ive seen to date, to reference, I ended up doing the following;

"

Date format =

Var _Y = value(Left('FILE PATH'[Fiscal Quarter], 4))

Var _d = 1

Var _QTR =

switch (value(right('File Path'[Fiscal Quarter], 1)), 1, 1, 2, 4, 3, 7, 4, 10)

RETURN

Date ( _y, _QTR, _d)"

Thanks for your support either way

[v-achippa]

Hi @lovelylemon,

Thank you for reaching out to Microsoft Fabric Community.

Thank you for confirming that the issue is resolved. I request you to please accept this post as "Accept as Solution", so that other community members who has the similar issue will find it more quickly.

Thanks and regards,

Anjan Kumar Chippa

[lbendlin]

In your data model include a calendar table. Mark it as a Date table.  Use the standard Time Intelligence functions. No need to do any string math contortions.

[lovelylemon]

Thank you for the reply - the issue I feel I have (or maybe not understanding), is linking the original data to the calendar table.

i.e. The lowest form of data I have on the origial data is "2024 Q1". How I create the logic to convert this to a usable date is causing the problem.

[lbendlin · Accepted solution]

The lowest form of data I have on the origial data is "2024 Q1"

Add a Date column to your data, for example the first day of that quarter.  Use the Date column to join to the calendar table.

[SundarRaj]

Hey @lovelylemon. Here's a solution you could look at for your query. I'll attach the images of the output, the input and the snippet of the code for your reference. Thanks

[lovelylemon]

Hi, this looks what I was looking for, though user incompetence is stopping me from actioning it (completly new to power query /BI) .

I can see the steps, but can you breakdown the actions so I understand it? I have tried to replicate the advanced editor but I got an error. 

[SundarRaj]

Sure @lovelylemon 

1. The first two steps basically imports the data and sets the data type.

2. List: Table.Transform Function helps me make alternations to the column I am working with which is Fiscal Quarter column in your case. What I am trying to do is basically split the text by a space into a List of 2 items i.e. 2024 and Q1. Post Splitting I am replacing any text with "Q" to a blank which aids me in havings inputs for a date.

3. Similarly, the next code simply just uses the #date function. Since we have a list of 2 items originally, to create a date, I would need a year, month and day. For the year, Number.From(_{0}), this essentially means I am extracting the first item of the list and converting it into a number and using it as a year input for #date.

4. Similar thing has been done for Month as well and day has been taken as 1 by default.

Let me know if you would need any more clarifications on any piece of code written. Thanks