Forum Discussion
Distinct count based on previous dates and grouped by other variables
change the column [all] in the expressione where
#"Added Custom 1" = List.Transform(#"Added Custom"[all],to [rollingQ]
#"Added Custom 1" = List.Transform(#"Added Custom"[rollingQ],but, please PLEASE, be carefull to the space in the text values. For instance Blue is not "Blue" but "Blue " and this cause problem.
I don't have now enough time to analyze and understand the real reason for these problems, but, for safety, find and remove the extra spaces
#########edited#############
use this code to try
let s1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZTNbsIwEIRfpcqZSKU3jqCqhwh6IBcqxCGA+0MtQEhpVZ6+Voyn9o4Xou4hsfAnz2ZnzHJZDMvnw1c5HBWDovHlVhPbmjv3rovVIEHWXbnF3Gzdcyb3/454MdYevt1iemEezcYzm0tFOguCtl1BqJb7xlcsFJqpmn35cO9+eA0VKU0l9NYVhBYEZD6plgzGEmSoFzG4PircyntXGX80GWrjA5WzKNMNOfRk1pJJLQKAXsghILtQGYcAffpii2bNSVOqJYI8SYeYSB3CfhRbtmh8PHHm0rkAQeJoLkB4/iQknOZO9KFckeFjkDnlayj6kT0/mdClx4DR0pQBaGxgqBdqJQ4cpUlXom4wlX80U7X72+kHpKYfhG40EDX9TIj/p7B/Nf2g9PRXrb0hZHsJ2d5C+jUDol8zINo1y5yh65x9CWb1Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Var1 = _t, Var2 = _t]), s2=Table.ReplaceValue(s1,"Blue ","Blue",Replacer.ReplaceText,{"Var1"}), #"Changed Type" = Table.TransformColumnTypes(s2,{{"Date", type date}},"en-US"), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <> null and [Date] <> ""), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date"}, {"all", each _},GroupKind.Local,(x,y)=>Number.From(y[Date]>=Date.AddMonths(x[Date],1))), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "rollingQ", each Table.Combine(List.LastN(List.FirstN(#"Grouped Rows"[all], List.PositionOf(#"Grouped Rows"[all],[all])),3))), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUQpWitWBc3yROT7InAgILyg1Ba4HwvZFYiOrgWqITM3JyS+H64FzfVG5aIpBmmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type 1" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Custom 1" = List.Transform(#"Added Custom"[rollingQ], (t)=> Table.AddColumn(#"Changed Type 1", "dist", each try Table.RowCount(Table.Distinct(Table.SelectRows(t, (r)=> r[Var1]=[Column1] and r[Var2]=[Column2] ),"ID" ))otherwise 0)), q1=Table.FromColumns({#"Added Custom"[Date], #"Added Custom 1"}), #"Expanded Column2" = Table.ExpandTableColumn(q1, "Column2", {"Column1", "Column2", "dist"}, {"Column2.Column1", "Column2.Column2", "Column2.dist"}), #"Added Custom 2" = Table.AddColumn(#"Expanded Column2", "mmm", each Date.MonthName([Column1],"it-IT")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom 2",{"Column1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"mmm", "Column1"}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Renamed Columns", {{"Column1", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Renamed Columns", {{"Column1", type text}}, "it-IT")[Column1]), "Column1", "Column2.dist") in #"Pivoted Column"The problem about space ending Blue was that I hand writed an auxiliary table for the colours and sizes and, of course, I couldn't realize there was a space after Blue in the table you linked
if you expect (is not clear to me) a sort of rolling info on previuos tree months:
let
s1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZTNbsIwEIRfpcqZSKU3jqCqhwh6IBcqxCGA+0MtQEhpVZ6+Voyn9o4Xou4hsfAnz2ZnzHJZDMvnw1c5HBWDovHlVhPbmjv3rovVIEHWXbnF3Gzdcyb3/454MdYevt1iemEezcYzm0tFOguCtl1BqJb7xlcsFJqpmn35cO9+eA0VKU0l9NYVhBYEZD6plgzGEmSoFzG4PircyntXGX80GWrjA5WzKNMNOfRk1pJJLQKAXsghILtQGYcAffpii2bNSVOqJYI8SYeYSB3CfhRbtmh8PHHm0rkAQeJoLkB4/iQknOZO9KFckeFjkDnlayj6kT0/mdClx4DR0pQBaGxgqBdqJQ4cpUlXom4wlX80U7X72+kHpKYfhG40EDX9TIj/p7B/Nf2g9PRXrb0hZHsJ2d5C+jUDol8zINo1y5yh65x9CWb1Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Var1 = _t, Var2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(s1,{{"Date", type date}},"en-US"),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <> null and [Date] <> ""),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date"}, {"all", each _},GroupKind.Local,(x,y)=>Number.From(y[Date]>=Date.AddMonths(x[Date],1))),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "rollingQ", each List.LastN(List.FirstN(#"Grouped Rows"[all], List.PositionOf(#"Grouped Rows"[all],[all])),3)),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUQpWitWBc3yROT7InAgILyg1Ba4HwvZFYiOrgWqITM3JyS+H64FzfVG5aIpBmmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type 1" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Added Custom 1" = List.Transform(#"Added Custom"[all], (t)=> Table.AddColumn(#"Changed Type 1", "dist", each Table.RowCount(Table.Distinct(Table.SelectRows(t, (r)=> r[Var1]=[Column1] and r[Var2]=[Column2]),"ID"))))
in
Table.FromColumns({#"Added Custom"[Date], #"Added Custom 1"})
Hi, Anonymous
I am trying the different solutions you've provided me. Thank you so much, they look much better than what I did 🙂
Comparing the last query with a manual calculation I've done in the excel, this how I'd like to get the month of July 2020:
The results are not exactly the same, the differences are in red color.
I'll try to understand why some totals are different.
As I need the last 3 months, to fill the rows for Jul-20 I am taking all the rows in Jun-20, May-20 and Apr-20.
Then I am filtering by the VAR1 and VAR2, and with the results I count the distinct IDs.
I have highlighted a case I think it's relevant: if I count all the IDs for Yellow+S, the total is 3 but, as far as there is an ID repeated (bbbbbb) it will be only counted once. So, in the row 01-Jul-20 | Yellow | S, the total I'd like to see is 2 instead of 3
Maybe I did something wrong when copying your query, I'll double check just in case.
Thanks again!! I really appreciate your help 🙂
change the column [all] in the expressione where
#"Added Custom 1" = List.Transform(#"Added Custom"[all],to [rollingQ]
#"Added Custom 1" = List.Transform(#"Added Custom"[rollingQ],but, please PLEASE, be carefull to the space in the text values. For instance Blue is not "Blue" but "Blue " and this cause problem.
I don't have now enough time to analyze and understand the real reason for these problems, but, for safety, find and remove the extra spaces
#########edited#############
use this code to try
let s1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZTNbsIwEIRfpcqZSKU3jqCqhwh6IBcqxCGA+0MtQEhpVZ6+Voyn9o4Xou4hsfAnz2ZnzHJZDMvnw1c5HBWDovHlVhPbmjv3rovVIEHWXbnF3Gzdcyb3/454MdYevt1iemEezcYzm0tFOguCtl1BqJb7xlcsFJqpmn35cO9+eA0VKU0l9NYVhBYEZD6plgzGEmSoFzG4PircyntXGX80GWrjA5WzKNMNOfRk1pJJLQKAXsghILtQGYcAffpii2bNSVOqJYI8SYeYSB3CfhRbtmh8PHHm0rkAQeJoLkB4/iQknOZO9KFckeFjkDnlayj6kT0/mdClx4DR0pQBaGxgqBdqJQ4cpUlXom4wlX80U7X72+kHpKYfhG40EDX9TIj/p7B/Nf2g9PRXrb0hZHsJ2d5C+jUDol8zINo1y5yh65x9CWb1Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Var1 = _t, Var2 = _t]), s2=Table.ReplaceValue(s1,"Blue ","Blue",Replacer.ReplaceText,{"Var1"}), #"Changed Type" = Table.TransformColumnTypes(s2,{{"Date", type date}},"en-US"), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <> null and [Date] <> ""), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date"}, {"all", each _},GroupKind.Local,(x,y)=>Number.From(y[Date]>=Date.AddMonths(x[Date],1))), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "rollingQ", each Table.Combine(List.LastN(List.FirstN(#"Grouped Rows"[all], List.PositionOf(#"Grouped Rows"[all],[all])),3))), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUQpWitWBc3yROT7InAgILyg1Ba4HwvZFYiOrgWqITM3JyS+H64FzfVG5aIpBmmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type 1" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Custom 1" = List.Transform(#"Added Custom"[rollingQ], (t)=> Table.AddColumn(#"Changed Type 1", "dist", each try Table.RowCount(Table.Distinct(Table.SelectRows(t, (r)=> r[Var1]=[Column1] and r[Var2]=[Column2] ),"ID" ))otherwise 0)), q1=Table.FromColumns({#"Added Custom"[Date], #"Added Custom 1"}), #"Expanded Column2" = Table.ExpandTableColumn(q1, "Column2", {"Column1", "Column2", "dist"}, {"Column2.Column1", "Column2.Column2", "Column2.dist"}), #"Added Custom 2" = Table.AddColumn(#"Expanded Column2", "mmm", each Date.MonthName([Column1],"it-IT")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom 2",{"Column1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"mmm", "Column1"}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Renamed Columns", {{"Column1", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Renamed Columns", {{"Column1", type text}}, "it-IT")[Column1]), "Column1", "Column2.dist") in #"Pivoted Column"The problem about space ending Blue was that I hand writed an auxiliary table for the colours and sizes and, of course, I couldn't realize there was a space after Blue in the table you linked
Hi Anonymous
That's fantastic!
It is exactly what I needed. I will adapt your query to my data, as I have many other columns and data processing needed but yes, the point I couldn't solve is this.
You're a star, thank you so much for your help 🙂