Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
11-24-2022 00:57 AM - last edited 11-24-2022 00:59 AM
Problem Statement: Table 1 has date ranges and table 2 has dates. We need to add a new column in table 2 which provides us with the count of rows that contains this date in table 1. In case of a null value consider today's date. This solution is needed in Power query
Table1: Ranges
Table2: Dates
Solution: We add a new column in Dates. This will use Table.SelectRows to filter the data. If statement and Date.FixedLocalNow to handle the null value of date2. and Table.RowCount to count rows
The new column formula is
let
_col = [Dates],
_table = Table.SelectRows(Ranges, each [Date1] <= _col and ( if [Date2] = null then DateTime.Date( DateTime.FixedLocalNow() ) else [Date2]) >= _col ),
_count = Table.RowCount(_table)
in
_count
This how the data look like, in Dates Table
You can find the blog here
The file is attached after signature
Find all my Medium blogs here
Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.
You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share