Forum Discussion
OrkA
4 years agoRegular Visitor
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...
- 4 years ago
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"
Vijay_A_Verma
4 years agoMost Valuable Professional
Can you please make a result table and post the result?
For row 2 - CY20Q3 and CY20Q4 both should come
- OrkA4 years agoRegular Visitor
Hi,
Yes you're right sorry.
Added results to original post.