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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MoshingBananas
New Member

Writing a query which standardize the days

Hello,

The following query column has a mixture of days / months / hours.

And I want to summarise it into 1 column where it involve only days.

I understand that I need to use an IF ELSE statement, but i am pretty lost from there. 

DaysFormula.jpg

Truly appreciate any sort of advice

1 ACCEPTED SOLUTION
artemus
Employee
Employee

First, a problem, how many days is a month, could be 28, 29, 30, or 31 depending which month and if it is a leap year. For this example I will assume one month is 30.436875 days.

 

Start by using "Enter Data" to create a mapping table of unit to number of days

day1
week7
month30.436875
hour0.15014739235743134753098878822799
year365.2425

 

Then:

1. Split your table with OperationDuration by space (first time only)

2. Split the first column by '-' (only once)

3. Change the two column types to number

4. Select the first two columns and under "Add Column" choose the Stastics button and choose Average

5. Do a merage on the table you entered and your source table using the right split column (from step1) and the first column of the table you enterd in. Use a fuzzy join.

6. Expand the merged column choosing the second column

7. Select the expanded column and the Average column and under "Add Column" choose multiply.

8. Remove all other columns. Any nulls in this column will be the result of an error.

View solution in original post

2 REPLIES 2
artemus
Employee
Employee

First, a problem, how many days is a month, could be 28, 29, 30, or 31 depending which month and if it is a leap year. For this example I will assume one month is 30.436875 days.

 

Start by using "Enter Data" to create a mapping table of unit to number of days

day1
week7
month30.436875
hour0.15014739235743134753098878822799
year365.2425

 

Then:

1. Split your table with OperationDuration by space (first time only)

2. Split the first column by '-' (only once)

3. Change the two column types to number

4. Select the first two columns and under "Add Column" choose the Stastics button and choose Average

5. Do a merage on the table you entered and your source table using the right split column (from step1) and the first column of the table you enterd in. Use a fuzzy join.

6. Expand the merged column choosing the second column

7. Select the expanded column and the Average column and under "Add Column" choose multiply.

8. Remove all other columns. Any nulls in this column will be the result of an error.

lbendlin
Super User
Super User

What about the crazy entries - 

 

6-24 months

2 weeks+

4.5 days annually

 

How are you planning to map these?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors