cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alan7lp
Helper III
Helper III

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_NOITEMRESULT
11111A2
11111B3
11111C2
11111D3
22222A2
22222B3
22222D3
22222E2
33333D3
33333B3
33333C2
33333E2
33333F1

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
1 ACCEPTED SOLUTION

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.

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

6 REPLIES 6
watkinnc
Super User
Super User

OK, now that I understand what you needed, you could do it with one function:

 

= let selections = TableName[ITEM] in Table.AddColumn(TableName, "Count", each List.Count(List.Contains(selections, [ITEM])))

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

If you need the original rows in a table column, just add an "All Rows" aggregation to your grouping. Now you have your groups, counts, and all associated rows for each.

 

--Nate

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

You can use the Group By function from the Transform tab. Just use SUP_NO and ITEM as your group fields, and choose "Count" as your aggregation. 
That's it!

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
CNENFRNL
Super User
Super User

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"

Screenshot 2021-07-09 203159.png

btw,

Count of SUPPLIERS on Item =
CALCULATE (
    DISTINCTCOUNT ( Fact_Supply_Qty[SUP_NO] ),
    ALLEXCEPT ( Fact_Supply_Qty, Fact_Supply_Qty[ITEM] )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

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:

 

alan7lp_0-1625922511941.png


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!

 

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.

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors