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

[Ekaterina_]

Hello everyone,

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

Date	Product	Status
07.03.2024	Book	Preparing
07.03.2024	Shoes	Preparing
08.03.2024	Book	Preparing
08.03.2024	Shoes	Delivering
08.03.2024	Dress	Preparing
09.03.2024	Book	Preparing
09.03.2024	Shoes	Delivered
09.03.2024	Dress	Delievering

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

Product	Preparing	Delivering
Book	3
Shoes	1	1
Dress	1	1

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!

[Vijay_A_Verma · Accepted solution]

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"

[Anonymous · Accepted solution]

Hi @Ekaterina_ ,

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

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

[Ekaterina_]

Hello @Anonymous 

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

[Vijay_A_Verma · Accepted solution]

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"

[Ekaterina_]

Hello @Vijay_A_Verma 

Thank you very much, it works like this! 🙂