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] ) ) Result:SUP_NOITEMRESULT11111A211111B311111C211111D322222A222222B322222D322222E233333D333333B333333C233333E233333F1ITEM 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. Each time an article repeats for different suppliers, the SUM of the different suppliers should be reflected in a new colum. Thanks in advance, hope the explanation is clear.Best regards,Alan

Anonymous

Not applicable

5 years ago

Solved

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

Result:

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.

Each time an article repeats for different suppliers, the SUM of the different suppliers should be reflected in a new colum.

Thanks in advance, hope the explanation is clear.

Best regards,
Alan

Need Help

CNENFRNL

5 years ago

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,

https://dax.guide/allexcept/

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.

Forum Discussion

Count of occurrences in Power Query