cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alisina
Helper III
Helper III

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

table1.PNG

 

Figure 2:
calculate.PNG

Figure 3:

table2.PNG

 

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 similar to using the calculate function in DAX shown in Figure 2.

In the end the counted rows in the Figure 1 (Table 1) will be unpivoted.

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @Alisina 

 

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.

image.png

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

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

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

6 REPLIES 6
Mariusz
Community Champion
Community Champion

Hi @Alisina 

 

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.

image.png

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

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

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

*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.

ChemEnger_0-1637074566488.png

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

ChemEnger_1-1637074649625.png

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

ChemEnger_2-1637074775025.png

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

ChemEnger_3-1637074827354.png

 

ChemEnger_5-1637075401357.png

4. But when I expand, it still gets counted

ChemEnger_4-1637075297389.png

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.

Mariusz
Community Champion
Community Champion

Hi @Alisina 

 

can you provide a small data samples reflecting both tables structure and based on this sample an expected outcome?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

hey,

Sure ive put it in filebin:

https://filebin.net/33dwl8b1ukm16qpu

Mariusz
Community Champion
Community Champion

Hi @Alisina 

 

So what is the outcome that you expect?

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski

 

That I can count rows from table2 using based on date value, which is the same in both table1 and table2.

I wanna count the rows in table2 Litra (how many of same kinds), the result is shown in table1.

Though they have no relation like in DAX (I dont want it done in DAX).

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors