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 ...
  • 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