Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Truly appreciate any sort of advice
Solved! Go to Solution.
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
day | 1 |
week | 7 |
month | 30.436875 |
hour | 0.15014739235743134753098878822799 |
year | 365.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.
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
day | 1 |
week | 7 |
month | 30.436875 |
hour | 0.15014739235743134753098878822799 |
year | 365.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.
What about the crazy entries -
6-24 months
2 weeks+
4.5 days annually
How are you planning to map these?