Power Query Across Table calculated column

amitchandak — Thu, 24 Nov 2022 08:59:44 GMT

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 
_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

Master Power BI

Expertise Power BI

Power BI For Tableau User

DAX for SQL Users

topic Power Query Across Table calculated column in Quick Measures Gallery

Power Query Across Table calculated column

Power Query Across Table calculated column