Power Query : customize column that count number of rows for each ROW

[omarelmb123]

Hello everyone, i want to execute this query 

I have product table with 3 columns ( Product Key, Date , sold )  

We can find for one unique productKey many records for different date values. 

     -i want to count the number of YES in the Sold column for each Product Key 

Pls how to do it in Power query ?!

Thank you.

[ronrsnfld · Accepted solution]

If I understand properly, you can do this in PQ by

• Group by Product Key
• Count the number of YES in each group
• Re-expand the table

Note that your example seems to be incorrect

• 31/2/2000 is not a valid date
• Key A3 only has a count of one (1), not three(3)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEystQ30jcyMDIAsiNdg5VidaAShkb6BoYgGUNkGSOQjCGSjJ8/RMIYZBZQwhhDiwlIxginjDG6TCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Key" = _t, Date = _t, Sold = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Product Key", type text}, {"Date", type date}, {"Sold", type text}}, "en-150"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Key"}, {
        {"All", each _, type table [Product Key=nullable text, Date=nullable date, Sold=nullable text]},
        {"Count", each List.Count(List.RemoveItems(_[Sold],{"NO"})), Int64.Type}
        }),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Date", "Sold"}, {"Date", "Sold"})
in
    #"Expanded All"

Data

Results

[omarelmb123]

Hello, that's an exemple of data set and result desired.

Product Key	Date	Sold	Count (Output wanted)
A1	31/2/2020	YES	2
A1	12/01/2021	YES	2
A2	11/01/2021	NO	0
A3	21/03/2021	YES	3
A4	22/03/2021	YES	3
A4	23/03/2021	YES	3

[ronrsnfld · Accepted solution]

If I understand properly, you can do this in PQ by

• Group by Product Key
• Count the number of YES in each group
• Re-expand the table

Note that your example seems to be incorrect

• 31/2/2000 is not a valid date
• Key A3 only has a count of one (1), not three(3)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEystQ30jcyMDIAsiNdg5VidaAShkb6BoYgGUNkGSOQjCGSjJ8/RMIYZBZQwhhDiwlIxginjDG6TCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Key" = _t, Date = _t, Sold = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Product Key", type text}, {"Date", type date}, {"Sold", type text}}, "en-150"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Key"}, {
        {"All", each _, type table [Product Key=nullable text, Date=nullable date, Sold=nullable text]},
        {"Count", each List.Count(List.RemoveItems(_[Sold],{"NO"})), Int64.Type}
        }),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Date", "Sold"}, {"Date", "Sold"})
in
    #"Expanded All"

Data

Results

[omarelmb123]

thank you it worked, Can you pls tell me how to get the last day of sold product ?

[ronrsnfld]

It would be the Max of the date column, either in the grouping aggregations or relative to the entire table depending on what you want for a result and how you want it presented.

[omarelmb123]

so i have to filter first the column sold on YES then operate list.MAX or there's another way to do it ?

[ronrsnfld]

For example, if you wanted to

• Add a column
• With date of last sold product
• for each product key

you would add an aggregation to the Table.Group command:

     {"Last Date Sold", each List.Max([Date]), type date}

If you want something else for results or presentation, then the solution would be different.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEystQ30jcyMDIAsiNdg5VidaAShkb6BoYgGUNkGSOQjCGSjJ8/RMIYZBZQwhhDiwlIxginjDG6TCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Key" = _t, Date = _t, Sold = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Product Key", type text}, {"Date", type date}, {"Sold", type text}}, "en-150"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Key"}, {
        {"All", each _, type table [Product Key=nullable text, Date=nullable date, Sold=nullable text]},
        {"Count", each List.Count(List.RemoveItems(_[Sold],{"NO"})), Int64.Type},
        {"Last Date Sold", each List.Max([Date]), type date}
        }),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Date", "Sold"}, {"Date", "Sold"})
in
    #"Expanded All"

[omarelmb123]

Hello, that s what i want to do.

• Add a column
• With date of last sold product
• for each product key

but doing it with the last formula it will calculate only last date for Sold or no Sold products. for the case if i had a product and it's been sold one time but he wasnt sold the last date. the agregation will show the wrong date. is there any way add a filter to the Sold column to have only the records with Yes values. 

[ronrsnfld · Accepted solution]

Sorry about that.

The new line should have been:

    {"Last Date Sold", (t)=> List.Max(Table.SelectRows(t, each [Sold] = "YES")[Date]), type date}

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEystQ30jcyMDIAsiNdg5VidaAShkb6BoYgGUNkGSOQjCGSjJ8/RMIYZBZQwhhDiwlIxginjDG6TCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Key" = _t, Date = _t, Sold = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Product Key", type text}, {"Date", type date}, {"Sold", type text}}, "en-150"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Key"}, {
        {"All", each _, type table [Product Key=nullable text, Date=nullable date, Sold=nullable text]},
        {"Count", each List.Count(List.RemoveItems(_[Sold],{"NO"})), Int64.Type},
        {"Last Date Sold", (t)=> List.Max(Table.SelectRows(t, each [Sold] = "YES")[Date]), type date}
        }),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Date", "Sold"}, {"Date", "Sold"})
in
    #"Expanded All"

[ronrsnfld]

I cannot answer that question since you have not indicated what you want for a result nor how you want it presented. It might require what you wrote, or it might be as simple as a single added line of code in power query.

[Mahesh0016]

@omarelmb123 

*If this post helps, please consider accept as solution to help other members find it more quickly.

[Mahesh0016]

@omarelmb123 
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.