How to separate each quarter in 3 lines with month and divide a specific number

[gluizqueiroz]

I have the following table:

Group	Qty	Quarter	Region
Eletronics	420	1°/2018	North
Food	600	1°/2018	North
Cloths	480	1°/2018	North
Eletronics	300	2°/2018	North
Food	360	2°/2018	North
Cloths	450	2°/2018	North

I need to divide each quarter in 3 lines with month and divide the column Qty by 3 for each month, like the following:

Group	Qty	Quarter	Region
Eletronics	140	jan/2018	North
Eletronics	140	feb/2018	North
Eletronics	140	mar/2018	North
Food	200	jan/2018	North
Food	200	feb/2018	North
Food	200	mar/2018	North
Cloths	160	jan/2018	North
Cloths	160	feb/2018	North
Cloths	160	mar/2018	North
Eletronics	100	apr/2018	North
Eletronics	100	may/2018	North
Eletronics	100	jun/2018	North
Food	120	apr/2018	North
Food	120	may/2018	North
Food	120	jun/2018	North
Cloths	150	apr/2018	North
Cloths	150	may/2018	North
Cloths	150	jun/2018	North

Is there any way to achieve this with Power Query? 

[v-cherch-msft · Accepted solution]

Hi @gluizqueiroz 

Please paste below formula in advanced editor to check the workaround.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1JLSnKz8tMLlbSUTIxMgCShoc26BsZGFoAmX75RSUZSrE60Upu+fkpQAEzA5wqnHPySzLApljgVINimzHYLCM8thmb4VSBsM0Uu5pYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, Qty = _t, Quarter = _t, Region = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Qty", Int64.Type}, {"Quarter", type text}, {"Region", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Quarter_New", each if [Quarter] = "1°/2018" then "jan/2018,feb/2018,mar/2018" else if [Quarter] = "2°/2018" then "apr/2018,may/2018,jun/2018" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Quarter"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Quarter_New", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Quarter_New.1", "Quarter_New.2", "Quarter_New.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Group", "Qty", "Region"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Divided Column" = Table.TransformColumns(#"Removed Columns1", {{"Qty", each _ / 3, type number}})
in
    #"Divided Column"

Regards,