Power Query Across Table calculated column
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
_countThis how the data look like, in Dates Table
You can find the file here.
You can find the video below
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
