Forum Discussion
Count of occurrences in Power Query
Hello,
I would like to know if there is a way to get the same result in Power Query as shown below (achieved with a calculated column in Power BI)
Calculated Column code:
Count of SUPPLIERS on Item =
CALCULATE (
DISTINCTCOUNT ( 'Fact_Supply_Qty'[SUP_NO] ),
FILTER (
ALLEXCEPT ( 'Fact_Supply_Qty', 'Fact_Supply_Qty'[ITEM] ),
'Fact_Supply_Qty'[SUP_NO] = 'Fact_Supply_Qty'[SUP_NO]
)
)
| SUP_NO | ITEM | RESULT |
| 11111 | A | 2 |
| 11111 | B | 3 |
| 11111 | C | 2 |
| 11111 | D | 3 |
| 22222 | A | 2 |
| 22222 | B | 3 |
| 22222 | D | 3 |
| 22222 | E | 2 |
| 33333 | D | 3 |
| 33333 | B | 3 |
| 33333 | C | 2 |
| 33333 | E | 2 |
| 33333 | F | 1 |
ITEM A appears for 2 different SUP_NO.
ITEM B appears for 3 different SUP_NO.
ITEM C appears for 2 different SUP_NO.
ITEM F appears for 1 SUP_NO.
Best regards,
Alan
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQBJR0lR6VYHQTPCYXnjMJzAfOMQACuD8ZzQuGhqnQF84xBAC4H4zmh8JxReKj63JRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SUP_NO = _t, ITEM = _t]), #"Grouped Rows" = Table.Group(Source, {"ITEM"}, {{"Count", each Table.RowCount(_), Int64.Type}}), NestedJoin = Table.NestedJoin(Source, "ITEM", #"Grouped Rows", "ITEM", "Grouped", JoinKind.LeftOuter), #"Expanded Grouped" = Table.ExpandTableColumn(NestedJoin, "Grouped", {"Count"}, {"Count"}) in #"Expanded Grouped"Despite of same results, those two DAX formulae are different. You might want to refre to the explanation in detail,
When used as a modifier in CALCULATE or CALCULATETABLE, ALLEXCEPT removes the filters from the expanded table specified in the first argument, keeping only the filters in the columns specified in the following arguments. When used as a table function, ALLEXCEPT materializes all the unique combinations of the columns in the table specified in the first argument that are not listed in the following arguments. In this case, the result only has the columns of the table and ignores the expanded table.
6 Replies
CNENFRNLCommunity Champion
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQBJR0lR6VYHQTPCYXnjMJzAfOMQACuD8ZzQuGhqnQF84xBAC4H4zmh8JxReKj63JRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SUP_NO = _t, ITEM = _t]), #"Grouped Rows" = Table.Group(Source, {"ITEM"}, {{"Count", each Table.RowCount(_), Int64.Type}}) in #"Grouped Rows"btw,
Count of SUPPLIERS on Item = CALCULATE ( DISTINCTCOUNT ( Fact_Supply_Qty[SUP_NO] ), ALLEXCEPT ( Fact_Supply_Qty, Fact_Supply_Qty[ITEM] ) )Hi CNENFRNL
Solution is good and I knew about the grouping functionality in Power Query. What I would like to achieve is to have the original format of the table with an added column showing those numbers from the grouping.
Basically, adding the result you just have got to a new column as a table where I can expand the selection and have back all the rows. In this data set example is not needed but on the original one every row has some different information that I need. If I leave it just by the grouping I will lose all that info. Something like:
About the DAX, what is the difference between one and the other? I have tested both and result is exactly the same.
Thanks once again!- CNENFRNL
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgQBJR0lR6VYHQTPCYXnjMJzAfOMQACuD8ZzQuGhqnQF84xBAC4H4zmh8JxReKj63JRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SUP_NO = _t, ITEM = _t]), #"Grouped Rows" = Table.Group(Source, {"ITEM"}, {{"Count", each Table.RowCount(_), Int64.Type}}), NestedJoin = Table.NestedJoin(Source, "ITEM", #"Grouped Rows", "ITEM", "Grouped", JoinKind.LeftOuter), #"Expanded Grouped" = Table.ExpandTableColumn(NestedJoin, "Grouped", {"Count"}, {"Count"}) in #"Expanded Grouped"Despite of same results, those two DAX formulae are different. You might want to refre to the explanation in detail,
When used as a modifier in CALCULATE or CALCULATETABLE, ALLEXCEPT removes the filters from the expanded table specified in the first argument, keeping only the filters in the columns specified in the following arguments. When used as a table function, ALLEXCEPT materializes all the unique combinations of the columns in the table specified in the first argument that are not listed in the following arguments. In this case, the result only has the columns of the table and ignores the expanded table.