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 a2025Q193a2025Q210a2025Q358a2025Q460b2025Q190b2025Q245b2025Q380b2025Q4107 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? a1/15/202531a2/15/202531a3/15/202531a4/15/20253.3a5/15/20253.3a6/15/20253.3a7/15/202519.3a8/15/202519.3a9/15/202519.3a10/15/202520a11/15/202520a12/15/202520

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 letSource = 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")inExpand Stéphane

ch_metglobal

New Member

1 year ago

Solved

Converting quarter data into months

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

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

1 year ago

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:

wdx223_Daniel
1 year ago
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}))

8 Replies

slorin

Super User

1 year ago

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

Super User

1 year ago

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:

v-achippa
Community Support
1 year ago
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
  Community Support
  1 year ago
  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
    Community Support
    1 year ago
    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
wdx223_Daniel
Community Champion
1 year ago
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}))
SundarRaj
Super User
1 year ago
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

dufoq3

Community Champion

1 year ago

Hi ch_metglobal, another approach:

Output

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

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

Forum Discussion

Converting quarter data into months

8 Replies

Related content

Quarter calculation upto selected month

Convert date to Quarter year

convert date to month name

Average Volume - Quarter over Quarter, Month over Month

Percent change - month over month, quarter over quarter