Transfer quarterly data to monthly

[Abdelkarim]

Hello Everyone,

I am new in Power BI and want your help to transfer quarterly data to monthly data by adding new rows and repeating the quarterly value to each month in the quarter. below is the example for the what I am looking for.

The current query

Required Query

Date	US - Total Benefits
1/1/2012	118
02/01/12	118
03/01/12	118
4/1/2012	118.7
05/01/12	118.7
06/01/12	118.7

[Abdelkarim]

Hi Rena, 

My source is web site and I have to apply few steps to get the date and value. It doesn't work when I apply your solution. So please find what I have below and apprecite if you can add your soultion to it 

let
Source = Web.BrowserContents("https://data.bls.gov/timeseries/CIU203S000000000I"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='table0'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4), TABLE[id='table0'] > * > TR > TD[colspan=""4""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column2", "TABLE[id='table0'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3), TABLE[id='table0'] > * > TR > TD[colspan=""4""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column3", "TABLE[id='table0'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(2), TABLE[id='table0'] > * > TR > TD[colspan=""4""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column4", "TABLE[id='table0'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(1), TABLE[id='table0'] > * > TR > TD[colspan=""4""]:not([rowspan]):nth-child(1):nth-last-child(1)"}}, [RowSelector="TABLE[id='table0'] > * > TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", type text}, {"Period", type text}, {"Estimate Value", type text}, {"Standard Error", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Standard Error"}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns",1),
#"Replaced Value" = Table.ReplaceValue(#"Removed Bottom Rows","Qtr1","1",Replacer.ReplaceText,{"Period"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Qtr2","4",Replacer.ReplaceText,{"Period"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Qtr3","7",Replacer.ReplaceText,{"Period"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Qtr4","10",Replacer.ReplaceText,{"Period"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value3",{"Period", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Estimate Value", "US - Total Benefits"}, {"Merged", "Date"}})
in
#"Renamed Columns"

[Anonymous · Accepted solution]

Hi @Abdelkarim ,

I updated the sample file base on your provided codes, please find the details in the attachment. 

let
Source = Web.BrowserContents("https://data.bls.gov/timeseries/CIU203S000000000I"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='table0'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4), TABLE[id='table0'] > * > TR > TD[colspan=""4""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column2", "TABLE[id='table0'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3), TABLE[id='table0'] > * > TR > TD[colspan=""4""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column3", "TABLE[id='table0'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(2), TABLE[id='table0'] > * > TR > TD[colspan=""4""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column4", "TABLE[id='table0'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(1), TABLE[id='table0'] > * > TR > TD[colspan=""4""]:not([rowspan]):nth-child(1):nth-last-child(1)"}}, [RowSelector="TABLE[id='table0'] > * > TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", type text}, {"Period", type text}, {"Estimate Value", type text}, {"Standard Error", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Standard Error"}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns",1),
#"Replaced Value" = Table.ReplaceValue(#"Removed Bottom Rows","Qtr1","1",Replacer.ReplaceText,{"Period"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Qtr2","4",Replacer.ReplaceText,{"Period"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Qtr3","7",Replacer.ReplaceText,{"Period"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Qtr4","10",Replacer.ReplaceText,{"Period"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value3",{"Period", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date},{"Estimate Value", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Estimate Value", "US - Total Benefits"}, {"Merged", "Date"}}),
 #"Added Custom" = Table.AddColumn(#"Renamed Columns", "End Date", each Date.EndOfMonth(
  Date.AddMonths(
    [Date],
    2
  )
)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each let StDt = [Date], 
  AllDates = {Number.From(StDt) .. Number.From([End Date])}, 
  StofMonthDates = List.Distinct(
    List.Select(
      List.InsertRange(List.Transform(AllDates, each Date.StartOfMonth(Date.From(_))), 0, {StDt}), 
      each Number.From(_) >= Number.From(StDt)
    )
  )
  in
  StofMonthDates),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom",{"Date", "End Date"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Date"}})
in
    #"Renamed Columns2"

Best Regards

[Abdelkarim]

Thank you so much 🙂

[Anonymous]

Hi @Abdelkarim ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdDRDcAgCEXRXfhuqCiWOotx/zWaiiSP3xNyCcxJcstdi1S6SOSldU3SRGwbLaNPSkEdLK6O7cdaDmrGF6OOwpqioR2jurFyw6hj44FRR+WaoqEPRvvGzun8wIbRQEvR0IHRZ6Nh8tC5yBDzP4/9u9cH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"US - Total Benefits" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"US - Total Benefits", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End Date", each Date.EndOfMonth(
  Date.AddMonths(
    [Date],
    2
  )
)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each let StDt = [Date], 
  AllDates = {Number.From(StDt) .. Number.From([End Date])}, 
  StofMonthDates = List.Distinct(
    List.Select(
      List.InsertRange(List.Transform(AllDates, each Date.StartOfMonth(Date.From(_))), 0, {StDt}), 
      each Number.From(_) >= Number.From(StDt)
    )
  )
  in
  StofMonthDates),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Date", "End Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}})
in
    #"Renamed Columns"

Best Regards