Power Query / Power pivot countifs from multiple tables

[Anonymous]

I have 2 tables with a bunch of data, corresponding in two columns with Attribute1 and date.

I'd like to create a table that:

- has two columns with the unique combinations of Date and Atrribute1 ( <- this I can manage in Power Query)

- A column that counts occurance of date/attribute combination in table1 ( <- this is where I get stuck 🙂

- A column that counts occurance of date/attribute combination in table2 

See screenshot for what I'm trying to achieve (desired result = Table_Count example 1)

Ideally, I'd like to create a power pivot where I could switch between table_count examples 1, 2 and 3. I'd need to create a bridging table for that, but no matter which combination I try of relations between unique dates, unique attributes and/or unique [date+attribute] I can't get this to work. 

I'm doing this in Excel btw, not PowerBI, but everything I google on this subject refers me to this site.

I can manage this in Excel easily (with formulas), but for the first time I'm working with large datasets that will grow to 1 mln records before the end of the year and the formula's ain't cutting it anymore (not for big data, and not for data that will be regularly updated, power query really seems the way to go with that).  Also, for the betterment of my understanding, I'd like to be able to solve this without resorting to columns that concatenate values of other columns. 

I've bought a couple of books , but haven't manged to figure this out in the past week - thank you for pointing me in the right direction! 

[artemus]

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))

[Anonymous]

Can you point me to the bug report / explanation? Because I've used it a couple of times and once had some very unexpected results.

[Jimmy801]

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

[Anonymous]

Thank you for the reply!

What I ended up doing was starting a blank query and basically doing source = table1 & table2 & table3 etc., and going from there.

Am I correct in understanding your code basically does the same? Is there a speed difference when refreshing the query?

Because I've discovered I can build most of the (what would probably considerd rather rudimentary) tables I need with the Power Query GUI, but it is speed that is severely bottlenecking me now. As a result of several query merges, and then referencing these queries ~20 times to make ~20 small grouped by tables in a simple dashboard, I'm finding that excel seems to recalculate the referenced query every time before it does a new group by (if that makes any sense), allowing me to go for a walk for ~10 mins. 

[ziying35]

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.

[Anonymous]

Thank you for the reply! I need to take some time to properly go through it and understand it though. 

[Jimmy801]

Hello @Anonymous 

good luck. Waiting for your feedback then

BR

Jimmy

[artemus]

O, what @ziying35  has looks good....

Just add a change column type to number to make sure it doesn't load as text.

[artemus]

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.

[Anonymous]

Thank you for your reply. I tried this as a calculated column in a table containing unique date/attribute combo's. This is what I get:

Also not sure how this would work as there is no mention of attribute, and I'm specifically looking for counting date/attribute combinations.

[artemus]

You would add this as a custom column to your table which has distinct date/attribute.

For attribute, simply replace [Date] with [Attribute] when you add another column.

[artemus]

If your looking for just the count of the combinations then, all you need to do, is apply a Group by on the main table, selecting both the Date and Attribute columns.

[Anonymous]

No I am looking for a table that has counts for unique dat/attribute combo's from two other tables. Pls let me know what is not clear about the screenshot I posted in the opening post, with the intended end result. Thank you! 🙂