Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

counting rows from another table in Power Query (NOT DAX)

  Figure 2: Figure 3:   I have this table Figure 1, which I need to generate a new column able to count rows from another column(another table i.e. Figure 3) in power query simi...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Anonymous 

     

    Ok, just follow the steps below.

    1.  While in Table1, go to Home ribbon and click on Merge Queries.
    2. Select Table2 and dates on both tables as below, this will add an extra column with Tables in it.

    3. Click on double arrow in your new column and follow the setup below.
     
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

Mariusz's avatar
Mariusz
Community Champion
7 years ago

Hi Anonymous 

 

Ok, just follow the steps below.

1.  While in Table1, go to Home ribbon and click on Merge Queries.
2. Select Table2 and dates on both tables as below, this will add an extra column with Tables in it.

3. Click on double arrow in your new column and follow the setup below.
 
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

  • ChemEnger's avatar
    ChemEnger
    Advocate V
    4 years ago

    *I know this is an old thread but it's exactly what I'm trying to achieve!

    Mariusz,

     

    I have tried exactly this approach (I thought this should be the solution before reading this post) but I get an incorrect value when there are no matches.

     

    1.  I have a table AuditRequestLog with a [Date Requested] column.

    2.  I build a separate table of a list of dates

    3. I merge & aggregate the [Date] column of this table with AuditRequestLog, based on the [Date Requested] column

    3a. I can see for an example an empty table for 17/11/2021

     

    4. But when I expand, it still gets counted

    I don't want to have to filter out the date table, as I want to be able to see zeroes.  This is in Excel and I did consider a Date Table in Power Pivot but there is more than one date column in AuditRequestLog that I want to measure against.

  • mustafaabh's avatar
    mustafaabh
    New Member
    2 years ago

    is it possible to put a criteria on this? Like day after?
    If you have 2 tables one that includes name and birthdates and another includes just dates and you want to count dates that are after the birthdate of each row