Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Think of this scenario. You check out a library book and have not yet returned it. In the database, it will have a Check Out date (start date) and should not have a Check In date (end date).
My SQL query has 2 date values: 1. Start Date 2. End Date. The end date cannot have any null values so it will auto populate with 1900-01-01 00:00:000.
CURRENT PROBLEM: This auto populate is messing up my calculations because I am looking to have NULL values where the end date is still pending.
FAILED SOLUTION ATTEMPT: I tried to replace the value in PBI but it will not let me proceed without a value to replace (NULL will not be accepted). I also tried to do a CASE in SQL but that didn't work either.
How would you approach this? Lets say you need to calculate how many library books are returned on a monthly/weekly/daily/hourly level...how would you approach this since there is a prepopulated field where there is suppose to be a NULL?
Solved! Go to Solution.
Hi @Anonymous
1.Write this code in Advanced editor
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",#datetime(1900, 1, 1, 0, 0, 0)," ",Replacer.ReplaceValue,{"end date"}),
#"Changed Type" is last step name
2.Transform "end date" column to "datetime" format
3.Click on "end date",
Add Column->add date, date only, then a new column [Date] is added
Close&&apply,
"calculate how many library books are returned on a monthly/weekly/daily/hourly level"
4. Create a calendar table, then create relationship based on my table[Date]
calendar = ADDCOLUMNS( CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"week",WEEKNUM([Date],2))
Add "year","month","week" in the "Row" fields.
As for hourly level, please provide more details with some data or expect result.
If problem "Inserting NULL Values", i would suggest you to post another one for hourly level result, so more engineers will involve in your problem.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
1.Write this code in Advanced editor
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",#datetime(1900, 1, 1, 0, 0, 0)," ",Replacer.ReplaceValue,{"end date"}),
#"Changed Type" is last step name
2.Transform "end date" column to "datetime" format
3.Click on "end date",
Add Column->add date, date only, then a new column [Date] is added
Close&&apply,
"calculate how many library books are returned on a monthly/weekly/daily/hourly level"
4. Create a calendar table, then create relationship based on my table[Date]
calendar = ADDCOLUMNS( CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"week",WEEKNUM([Date],2))
Add "year","month","week" in the "Row" fields.
As for hourly level, please provide more details with some data or expect result.
If problem "Inserting NULL Values", i would suggest you to post another one for hourly level result, so more engineers will involve in your problem.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |