Converting quarter data into months

[ch_metglobal]

Hi all-- I thought I was able to easily perform this transformation but realized the dates make it a bit more complicated for me. 

I have the following dataset where I need to convert into monthly data

a	2025Q1	93
a	2025Q2	10
a	2025Q3	58
a	2025Q4	60
b	2025Q1	90
b	2025Q2	45
b	2025Q3	80
b	2025Q4	107

but need it to be converted into something like the following but I'm not entirely sure if I need to create an entire calendar table to handle this conversion with a table merge?

a	1/15/2025	31
a	2/15/2025	31
a	3/15/2025	31
a	4/15/2025	3.3
a	5/15/2025	3.3
a	6/15/2025	3.3
a	7/15/2025	19.3
a	8/15/2025	19.3
a	9/15/2025	19.3
a	10/15/2025	20
a	11/15/2025	20
a	12/15/2025	20

[slorin · Accepted solution]

Hi @ch_metglobal 

With "Your_Source"

Column1	YYYYQN	Value
a	2025Q1	93
a	2025Q2	10
a	2025Q3	58
a	2025Q4	60
b	2025Q1	90
b	2025Q2	45
b	2025Q3	80
b	2025Q4	107

let
Source = Your_Source,
Transform = Table.TransformColumns(Source,
   {{"YYYYQN", (x) => List.Transform({1, 2, 3}, 
       each #date(Number.From(Text.Start(x,4)), (Number.From(Text.End(x,1))-1)*3+_, 15)), type list},
    {"Value", each _ /3, type number}}),
Expand = Table.ExpandListColumn(Transform, "YYYYQN")
in
Expand

 Stéphane

[ronrsnfld · Accepted solution]

Given your data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDINNAQyLI2VYnWQxIyADEMDVDFjIMPUAlXMBMgwg6hLQjEPTQxknokpqhjIPAs0dSZge82VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Quarter = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Quarter", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1","Quarter"}, {
        {"Monthly", (t)=>
            [a=List.Sum(t[Amount]) / 3,
             b=#date(Number.From(Text.Start(t[Quarter]{0},4)), (Number.From(Text.End(t[Quarter]{0},1))-1)*3+1,15 ),
             c=List.Accumulate({0..2},{},(s,c)=> s & {Date.AddMonths(b,c)}),
             d=List.Transform(List.Zip({c, List.Repeat({a},3)}), each Record.FromList(_,{"Date","Amount"}))
             
             ][d], type {[Date=date, Amount=number]}
            
            }}),
    #"Expanded Monthly" = Table.ExpandListColumn(#"Grouped Rows", "Monthly"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Monthly",{"Quarter"}),
    #"Expanded Monthly1" = Table.ExpandRecordColumn(#"Removed Columns", "Monthly", {"Date", "Amount"})
in
    #"Expanded Monthly1"

Produces:

[dufoq3]

Hi @ch_metglobal, another approach:

Output

v1

let
    Source = Table.FromColumns(List.Split(Text.Split("a, a, a, a, b, b, b, b, 2025Q1, 2025Q2, 2025Q3, 2025Q4, 2025Q1, 2025Q2, 2025Q3, 2025Q4", ", ") & {93, 10, 58, 60, 90, 45, 80, 107}, 8)),
    Transformed = Table.FromRows(List.TransformMany(Table.ToRows(Source),
        each [ a = Number.From(Text.AfterDelimiter(_{1}, "Q")),
               b = List.Transform({a*3-2..a*3}, (x)=> Date.FromText(Text.Combine({Text.Start(_{1}, 4), Text.From(x), "15"}, "-"), [Format="yyyy-M-%d"]))
             ][b],
        (x,y)=> {x{0}, y, x{2}/3}))
in
    Transformed

v2

let
    Source = Table.FromColumns(List.Split(Text.Split("a, a, a, a, b, b, b, b, 2025Q1, 2025Q2, 2025Q3, 2025Q4, 2025Q1, 2025Q2, 2025Q3, 2025Q4", ", ") & {93, 10, 58, 60, 90, 45, 80, 107}, 8)),
    Date = Table.TransformColumns(Source, {{"Column2", each 
        [ a = Number.From(Text.AfterDelimiter(_, "Q")),
          b = List.Transform({a*3-2..a*3}, (x)=> Date.FromText(Text.Combine({Text.Start(_, 4), Text.From(x), "15"}, "-"), [Format="yyyy-M-%d"]))
        ][b]}}),
    Ad_Value = Table.AddColumn(Date, "Value", each [Column3] / List.Count([Column2]), type number),
    ExpandedColumn2 = Table.ExpandListColumn(Ad_Value, "Column2"),
    RemovedColumns = Table.RemoveColumns(ExpandedColumn2,{"Column3"})
in
    RemovedColumns

[SundarRaj]

Hi @ch_metglobal , another solution you can possibly look at. Thanks!

let
Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}}),
Custom1 = Table.FromRows(List.Repeat(Table.ToRows(#"Changed Type"),3)),
#"Grouped Rows" = Table.Combine(Table.Group(Custom1, {"Column1", "Column2", "Column3"}, {{"All", each _}})[All]),
Custom2 = Table.Group(Table.TransformColumns(#"Grouped Rows",{"Column3", each Number.Round(_ / 3,1)}),{"Column1"},{{"All",each Table.AddIndexColumn(_,"Index",1,1)}}),
Custom3 = Table.Combine(Table.TransformColumns(Custom2,{"All", each Table.AddIndexColumn(_,"Month",0,1)})[All]),
Custom4 = Table.TransformColumns(Custom3,{"Month", each #date(Number.From(Text.Start(Custom3{_}[Column2],4)),Custom3{_}[Index],15)}),
Custom5 = Custom4[[Column1],[Column3],[Month]]
in
Custom5

[wdx223_Daniel · Accepted solution]

NewStep=#table(Table.ColumnNames(YourSourceName),List.TransfromMany(Table.ToRows(YourSourceName),each let a=Text.Split(_{1},"Q") in List.Transform({1..3},each #date(Number.From(a{0}),Number.From(a{1})*3-3+_,15)),(x,y)=>{x{0},y,x{2}/3}))

[v-achippa]

Hi @ch_metglobal,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ronrsnfld and @slorin for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? or let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @ch_metglobal,

We wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @ch_metglobal,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[ronrsnfld · Accepted solution]

Given your data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDINNAQyLI2VYnWQxIyADEMDVDFjIMPUAlXMBMgwg6hLQjEPTQxknokpqhjIPAs0dSZge82VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Quarter = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Quarter", type text}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1","Quarter"}, {
        {"Monthly", (t)=>
            [a=List.Sum(t[Amount]) / 3,
             b=#date(Number.From(Text.Start(t[Quarter]{0},4)), (Number.From(Text.End(t[Quarter]{0},1))-1)*3+1,15 ),
             c=List.Accumulate({0..2},{},(s,c)=> s & {Date.AddMonths(b,c)}),
             d=List.Transform(List.Zip({c, List.Repeat({a},3)}), each Record.FromList(_,{"Date","Amount"}))
             
             ][d], type {[Date=date, Amount=number]}
            
            }}),
    #"Expanded Monthly" = Table.ExpandListColumn(#"Grouped Rows", "Monthly"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Monthly",{"Quarter"}),
    #"Expanded Monthly1" = Table.ExpandRecordColumn(#"Removed Columns", "Monthly", {"Date", "Amount"})
in
    #"Expanded Monthly1"

Produces:

[slorin · Accepted solution]

Hi @ch_metglobal 

With "Your_Source"

Column1	YYYYQN	Value
a	2025Q1	93
a	2025Q2	10
a	2025Q3	58
a	2025Q4	60
b	2025Q1	90
b	2025Q2	45
b	2025Q3	80
b	2025Q4	107

let
Source = Your_Source,
Transform = Table.TransformColumns(Source,
   {{"YYYYQN", (x) => List.Transform({1, 2, 3}, 
       each #date(Number.From(Text.Start(x,4)), (Number.From(Text.End(x,1))-1)*3+_, 15)), type list},
    {"Value", each _ /3, type number}}),
Expand = Table.ExpandListColumn(Transform, "YYYYQN")
in
Expand

 Stéphane