Power Query / Power pivot countifs from multiple tables

Hi, Anonymous 

Try this:

// fx
(tbl_name)=>
let
    Source = Excel.CurrentWorkbook(){[Name=tbl_name]}[Content],
    group = Table.Group(Source, {"Date", "Attribute"}, {{"Count_"&Text.Select(tbl_name, {"0".."9"}), each Table.RowCount(_), Int64.Type}})
in
    group

// output
let
    Source = List.Transform({"Table_1", "Table_2"}, fx),
    fnTrans = (tbl)=> 
                Record.Combine(
                    List.Transform(
                        List.Skip(Table.ColumnNames(tbl),2), 
                        each Record.FromList(
                               {List.Last(List.RemoveNulls(Table.Column(tbl, _)), null)},
                               {_}
                             )
                    )  
                ),
    tblCmb = Table.Combine(Source),
    group = Table.Group(tblCmb, {"Date", "Attribute"}, {"t", fnTrans}),
    expd = Table.ExpandRecordColumn(group, "t", {"Count_1", "Count_2"}),
    sort = Table.Sort(expd,{{"Date", Order.Ascending}}),
    chType = Table.TransformColumnTypes(sort,{{"Date", type date}})
in
    chType

Paste my two query codes directly into the Power Query editor of the Excel file you provided as an example, and follow the screenshot below:

If you want the date to be displayed as in the example you provided, it is recommended to set it in the Excel worksheet by Format Cells.

Hello Anonymous 

check out this solution. Should be a bit easier to understand. Be aware that it contains also two datasets of tables. Also in order that everyone can try it. Just substitute theses datasets with your Excel-tables

let
    Table1 = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDSMzJQ0lFyUorVAQoYoQsYwwUcIQIm6AKm6AJmcAFnpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Atribute = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date",  type date, "de-DE"}, {"Atribute", type text}})
    in
        #"Changed Type",

    Table2 = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDSMzJQ0lFyUorVAQoYwQVc0AWIVmGMLBALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Atribute = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date, "de-DE"}, {"Atribute", type text}})
    in
        #"Changed Type",

    CombineAndDistinct = Table.Distinct(Table.Combine({Table1,Table2})),
    AddCountForTable1 = Table.AddColumn
    (
        CombineAndDistinct,
        "Count Table1",
        (addrec)=> Table.RowCount(Table.SelectRows(Table1, each Record.SelectFields(_, {"Date", "Atribute"})  = Record.SelectFields(addrec, {"Date", "Atribute"}) ))
    ),
    AddCountForTable2 = Table.AddColumn
    (
        AddCountForTable1,
        "Count Table2",
        (addrec)=> Table.RowCount(Table.SelectRows(Table2, each Record.SelectFields(_, {"Date", "Atribute"}) =  Record.SelectFields(addrec, {"Date", "Atribute"}) ))
    )
in
    AddCountForTable2

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

You can use something like:

List.Count(List.Select(Table_1[Date], each _ = [Date]))

That is:

List.Count - Count items in a list

- List.Select - Only include item in the list that match a criteria

- - Table_1[Data] - Get the column "Data" for Table_1 as a list

- - each _ = [Date] - The criteria, for each item in the list, check if it "_" is equal to the current row's Date field.

There is a bug with combining tables in the Power Query editor right now. As a mitigation you can use (assuming all tables have the same schema):

#table(Value.Type(table1), Table.ToRows(table1) & Table.ToRows(table2) & Table.ToRows(table3))