Forum Discussion

OrkA's avatar
OrkA
Regular Visitor
4 years ago
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...
  • Vijay_A_Verma's avatar
    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"