Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ekaterina_
Helper I
Helper I

Counting occurence of a value in a column depending on other value

Hello everyone,

 

I have some trouble with creating a new table out of this table:

DateProductStatus
07.03.2024BookPreparing 
07.03.2024ShoesPreparing
08.03.2024BookPreparing
08.03.2024ShoesDelivering 
08.03.2024DressPreparing 
09.03.2024BookPreparing
09.03.2024ShoesDelivered
09.03.2024DressDelievering

 

What I want to get is a table which should count for how many times a product occurs with the corresponding status like this:

ProductPreparing Delivering 
Book3 
Shoes11
Dress 11

When I am using a query like this "GroupedRows = Table.Group(#"Changed Type", {"Product", "Status"}, {{"Preparing", each Table.RowCount(TableDistinct(_)), Int64.Type}, {"Delivering", each Table.RowCount(TableDistinct(_)), Int64.Type}}) it counts the Total occurence of the value in the table, so this doesn't help me.

 

I hope someone could provide me a solution. 

 

Thank you in advance!

2 ACCEPTED SOLUTIONS
Vijay_A_Verma
Super User
Super User

I will give one simple way to do it.

Remove Date column

Duplicate product column

Pivot on Status

Select first 3 columns

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Date"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Product", "Product - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Status]), "Status", "Product - Copy", List.Count),
    #"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"Product", "Preparing", "Delivering"})
in
    #"Removed Other Columns"

View solution in original post

Anonymous
Not applicable

Hi @Ekaterina_ ,

Your previous thought works, just need to filter the Group's table next.

vcgaomsft_0-1710383201176.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31jcyMDJR0lFyys/PBlIBRakFiUWZeemHFijF6qCoCM7ITy1GVgJWYIHbCHR5mAEuqTmZZalIliCpcSlKLS7G4g5LAtZY4rQmNQVdAcwOVHfEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}, {"Status", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Product"}, {{"Preparing", each Table.RowCount(Table.SelectRows(_, each [Status]="Preparing")), Int64.Type}, {"Delivering", each Table.RowCount(Table.SelectRows(_, each [Status]="Delivering")), Int64.Type}})
in
    #"Grouped Rows"

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @Ekaterina_ ,

Your previous thought works, just need to filter the Group's table next.

vcgaomsft_0-1710383201176.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc31jcyMDJR0lFyys/PBlIBRakFiUWZeemHFijF6qCoCM7ITy1GVgJWYIHbCHR5mAEuqTmZZalIliCpcSlKLS7G4g5LAtZY4rQmNQVdAcwOVHfEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}, {"Status", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Product"}, {{"Preparing", each Table.RowCount(Table.SelectRows(_, each [Status]="Preparing")), Int64.Type}, {"Delivering", each Table.RowCount(Table.SelectRows(_, each [Status]="Delivering")), Int64.Type}})
in
    #"Grouped Rows"

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Hello @Anonymous 

Thank you very much for the help, with this option it works also perfectly! 😀

Vijay_A_Verma
Super User
Super User

I will give one simple way to do it.

Remove Date column

Duplicate product column

Pivot on Status

Select first 3 columns

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Date"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Product", "Product - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Status]), "Status", "Product - Copy", List.Count),
    #"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"Product", "Preparing", "Delivering"})
in
    #"Removed Other Columns"

Hello @Vijay_A_Verma 

 

Thank you very much, it works like this! 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.