skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Power Query Across Table calculated column

    Power Query Across Table calculated column

    11-24-2022 00:57 AM - last edited 11-24-2022 00:59 AM

    Super User amitchandak
    Super User
    864 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    amitchandak
    Super User amitchandak
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Power Query Across Table calculated column

    ‎11-24-2022 12:57 AM

    Power Query Across Table calculated column

    Power BI(77).jpg

     

    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

    amitchandak_1-1669279729518.png

     

    Table2: Dates

    amitchandak_2-1669279729518.png

     

    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

    amitchandak_3-1669279729503.png

     

    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



    !! Microsoft Fabric !!
    Microsoft Power BI Learning Resources, 2023 !!
    Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
    Power Quer cross Table OPs.pbix
    Labels:
    • Labels:
    • Filters
    • Other
    • Time Intelligence
    Message 1 of 1
    864 Views
    0
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices