Forum Discussion

Abdelkarim's avatar
Abdelkarim
Frequent Visitor
3 years ago
Solved

Transfer quarterly data to monthly

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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