Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
If I understand properly, you can do this in PQ by
Note that your example seems to be incorrect
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 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 |
If I understand properly, you can do this in PQ by
Note that your example seems to be incorrect
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
thank you it worked, Can you pls tell me how to get the last day of sold product ?
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.
so i have to filter first the column sold on YES then operate list.MAX or there's another way to do it ?
For example, if you wanted to
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"
Hello, that s what i want to do.
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"
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.
*If this post helps, please consider accept as solution to help other members find it more quickly.
@omarelmb123
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.