Forum Discussion
Solved
Power Query Count By Quarter
Hi, Assume I have the next table: Tool ID Build Qtr Ship Qtr 1 CY22Q1 CY22Q3 2 CY20Q3 CY21Q1 3 CY22Q1 CY22Q2...
Use this. I have assumed that century is 2000, hence 22 will be 2022...This I have done for the sake of simplicity otherwise I can fit in the logic what should be treated as 1900 and what 2000 but I have avoided this and made a simple assumption.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKONDIKhDOMlWJ1opWMIFwDIBfMMAQqAIkbY6g3AoubILgI8VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ToolID = _t, BuildQtr = _t, ShipQtr = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ToolID", Int64.Type}, {"BuildQtr", type text}, {"ShipQtr", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "BuildQtrDate", each Date.FromText("20"&Text.Middle([BuildQtr],2,2)&"-"&Text.From(Number.From(Text.End([BuildQtr],1))*3)&"-1")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "ShipQtrDate", each Date.FromText("20"&Text.Middle([ShipQtr],2,2)&"-"&Text.From(Number.From(Text.End([ShipQtr],1))*3)&"-1")), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ListOfQuartersDate", each List.Generate(()=>[x=[BuildQtrDate],y=[ShipQtrDate]], each [x]<[y], each [x=Date.AddQuarters([x],1),y=[y]], each [x])), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each List.Transform([ListOfQuartersDate],(i)=>"CY"&Text.End(Text.From(Date.Year(i)),2)&"Q"&Text.From(Date.Month(i)/3))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Custom", Order.Ascending}}) in #"Sorted Rows"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKONDIKhDOMlWJ1opWMIFwDIBfMMAQqAIkbY6g3AoubILgI8VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ToolID = _t, BuildQtr = _t, ShipQtr = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ToolID", Int64.Type}, {"BuildQtr", type text}, {"ShipQtr", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [BuildQtr]<[ShipQtr] then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"BuildQtr"}, {{"Total", each List.Sum([Custom]), type number}})
in
#"Grouped Rows"
Thanks but its more complicated.
I want to count all the quarters between Build Qtr and Ship Qtr.
take a look at the example again please.
Use this. I have assumed that century is 2000, hence 22 will be 2022...This I have done for the sake of simplicity otherwise I can fit in the logic what should be treated as 1900 and what 2000 but I have avoided this and made a simple assumption.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKONDIKhDOMlWJ1opWMIFwDIBfMMAQqAIkbY6g3AoubILgI8VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ToolID = _t, BuildQtr = _t, ShipQtr = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ToolID", Int64.Type}, {"BuildQtr", type text}, {"ShipQtr", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "BuildQtrDate", each Date.FromText("20"&Text.Middle([BuildQtr],2,2)&"-"&Text.From(Number.From(Text.End([BuildQtr],1))*3)&"-1")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "ShipQtrDate", each Date.FromText("20"&Text.Middle([ShipQtr],2,2)&"-"&Text.From(Number.From(Text.End([ShipQtr],1))*3)&"-1")), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ListOfQuartersDate", each List.Generate(()=>[x=[BuildQtrDate],y=[ShipQtrDate]], each [x]<[y], each [x=Date.AddQuarters([x],1),y=[y]], each [x])), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each List.Transform([ListOfQuartersDate],(i)=>"CY"&Text.End(Text.From(Date.Year(i)),2)&"Q"&Text.From(Date.Month(i)/3))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Custom", Order.Ascending}}) in #"Sorted Rows"