Forum Discussion
jcastr02
1 year agoPost Prodigy
Historical Log support
Hello I have a list of stores and respective different types of support provided along with the reporting month when that support was provided. I need to create a historical log of when support star...
- 1 year ago
A different method, using GroupKind.Local:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZI7DoNADESvEm2NBPZ+3aenSYe4/zWCtIHsZ+wCIb0ngzye43CpiFvc63qIVlp5Y3bn0nFWeMV+wIyxxzhgHGf82d9E1zvhiYxxwVgwpk3hyq4/HHAEI/YzvpcKeCJinPQPZV0VXYmu7kigA/s/DoTwuKoijq1iFi7gVwwlW7I93eDYcN5wwXARuOZocCYbrhhODNd3WYkTy/ZyODHovOGC4SJwU83/MzLWvFdFV6KrvuaDUwKRqeaDw32WrurnFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, #"PCC Support" = _t, #"Reporting Month" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Store", Int64.Type}, {"PCC Support", type text}, {"Reporting Month", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Store","PCC Support"}, { {"Start", each List.Min([Reporting Month]), type nullable date}, //Assuming "Last reporting month" is the month preceding the current month {"End", each [a=Date.EndOfMonth(List.Max([Reporting Month])), b=Date.IsInPreviousNMonths(a,1), c=if b then null else a][c], type nullable date}}, GroupKind.Local), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([PCC Support] <> " ")) in #"Filtered Rows"
ronrsnfld
1 year agoSuper User
A different method, using GroupKind.Local:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZI7DoNADESvEm2NBPZ+3aenSYe4/zWCtIHsZ+wCIb0ngzye43CpiFvc63qIVlp5Y3bn0nFWeMV+wIyxxzhgHGf82d9E1zvhiYxxwVgwpk3hyq4/HHAEI/YzvpcKeCJinPQPZV0VXYmu7kigA/s/DoTwuKoijq1iFi7gVwwlW7I93eDYcN5wwXARuOZocCYbrhhODNd3WYkTy/ZyODHovOGC4SJwU83/MzLWvFdFV6KrvuaDUwKRqeaDw32WrurnFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, #"PCC Support" = _t, #"Reporting Month" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Store", Int64.Type}, {"PCC Support", type text}, {"Reporting Month", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Store","PCC Support"}, {
{"Start", each List.Min([Reporting Month]), type nullable date},
//Assuming "Last reporting month" is the month preceding the current month
{"End", each [a=Date.EndOfMonth(List.Max([Reporting Month])),
b=Date.IsInPreviousNMonths(a,1),
c=if b then null else a][c],
type nullable date}},
GroupKind.Local),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([PCC Support] <> " "))
in
#"Filtered Rows"