Countrows & Lookup

[1984]

Hi

I have a query. I have a table with MMM-YY for 12 months, I then have four other tables that are linked by relationship. I need to add a column to the MMM-YY table that looks across the other tables and counts how many rows meet the criteria I have done this across one but not multiple by relationship. So for example look in table 1 and look at column1 and see if "xxx" is a value then look at table 2 and see if the corresponding item in column 2 has value "xxx" and so on…
If all meet the said criteria count how many rows

[1984]

@edhans Do you have an example output that you achieved with this?

[edhans]

Hi @1984 
Create two tables. See directions below for pasting in this code.
Call this table "Items Sold" - and this is the code for it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7JCQAgDAR7ydsmEjVNBPtvQ8lrYPEzhLBXlaWdUTab0Vxggt7ccv+8VC5oHC6XLqaFpKX0MoerdGdAr8tDPq/rXA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t])
in
    Source

Create a 2nd table called "Item Count" and this is the code for that:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6gkk3pdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t]),
    CountRows =
        Table.AddColumn(
            Source,
            "Row Count",
            each
                let
                    varItem = [Item]
                in
            Table.RowCount(
                Table.SelectRows(
                    #"Items Sold", each [Item] = varItem
                )
            )
        )
in
    CountRows

This 2nd table looks at the current item (A-F) and selects the records from the "Items Sold" table that match the current row, then counts the rows in the other table.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[1984]

Hi @edhans 

I get the logic and this would work if I was to have just one table, however I have four tables linked by relationship and it needs to follow each on along to check the criteria is met upon multiple table then count as one if it is

[edhans]

You are going to have to provide more info. There are no relationships in Power Query, so not sure what you are referring to here.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[edhans]

Any update on this @1984 

[edhans]

You can do this using the Table.SelectRows() function.

    CountRows =
        Table.AddColumn(
            #"Added Custom1",
            "Row Count",
            each
                let
                    varDate = [Date]
                in
            Table.RowCount(
                Table.SelectRows(
                    Table1, each [Date] = varDate
                )
            )
        )
in
    CountRows

The above is a copy from the Advanced Editor. The CountRows step is adding a column to this table.
It traps the [Date] value (what I am looking up) then compared it to the [Date] column in a different table - Table1 in this case. When they match, it creates a table of just those rows, then counts the rows.

Warning - this will not be performant on large datasets. Power Query is pretty bad at table scans. This would be near instantaneous in DAX. But if the data isn't too large, Power Query can handle it.