Forum Discussion
Solved
Power Query : customize column that count number of rows for each ROW
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. ...
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
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"
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.
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"