Forum Discussion
Transfer quarterly data to monthly
- Anonymous3 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
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"
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
- Abdelkarim3 years agoFrequent Visitor
Thank you so much 🙂