Create a gist now

Instantly share code, notes, and snippets.

M code to obtain SQLBlog stats
let
Source = Web.Contents("http://sqlblog.com/blogs/Opml.aspx", [Headers=[#"Content-Type"="application/xml"]]),
#"Imported XML" = Xml.Tables(Source),
body = #"Imported XML"{0}[body],
outline = body{0}[outline],
#"Changed Type" = Table.TransformColumnTypes(outline,{{"Attribute:text", type text}}),
outline1 = #"Changed Type"{0}[outline],
#"Changed Type1" = Table.TransformColumnTypes(outline1,{{"Attribute:type", type text}, {"Attribute:text", type text}, {"Attribute:title", type text}, {"Attribute:description", type text}, {"Attribute:xmlUrl", type text}, {"Attribute:htmlUrl", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute:type"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute:text", "blogText"}, {"Attribute:title", "blogTitle"}, {"Attribute:description", "blogDescription"}, {"Attribute:xmlUrl", "blogRssUrl"}, {"Attribute:htmlUrl", "blogUrl"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Web.Page(Web.Contents([blogUrl]))),
#"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Caption", "Data"}, {"Custom.Caption", "Custom.Data"}),
#"Filtered Rows" = Table.SelectRows(#"Expand Custom", each ([Custom.Caption] = "Document")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom.Caption"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom.Data", "blogDOM"}}),
#"Expand blogDOM" = Table.ExpandTableColumn(#"Renamed Columns1", "blogDOM", {"Children"}, {"blogDOM.Children"}),
#"Expand blogDOM.Children" = Table.ExpandTableColumn(#"Expand blogDOM", "blogDOM.Children", {"Name", "Children"}, {"blogDOM.Children.Name", "blogDOM.Children.Children"}),
#"Filtered Rows1" = Table.SelectRows(#"Expand blogDOM.Children", each ([blogDOM.Children.Name] = "BODY")),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows1",{"blogDOM.Children.Name"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"blogDOM.Children.Children", "blogBody"}}),
#"Expand blogBody" = Table.ExpandTableColumn(#"Renamed Columns2", "blogBody", {"Children"}, {"blogBody.Children"}),
#"Expand blogBody.Children" = Table.ExpandTableColumn(#"Expand blogBody", "blogBody.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Kind", "blogBody.Children.Name", "blogBody.Children.Children", "blogBody.Children.Text"}),
#"Filtered Rows2" = Table.SelectRows(#"Expand blogBody.Children", each ([blogBody.Children.Name] = "DIV")),
#"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows2",{"blogBody.Children.Kind", "blogBody.Children.Name", "blogBody.Children.Text"}),
#"Expand blogBody.Children.Children" = Table.ExpandTableColumn(#"Removed Columns3", "blogBody.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Kind", "blogBody.Children.Children.Name", "blogBody.Children.Children.Children", "blogBody.Children.Children.Text"}),
#"Removed Columns4" = Table.RemoveColumns(#"Expand blogBody.Children.Children",{"blogBody.Children.Children.Text", "blogBody.Children.Children.Kind"}),
#"Filtered Rows3" = Table.SelectRows(#"Removed Columns4", each ([blogBody.Children.Children.Name] = "DIV")),
#"Removed Columns5" = Table.RemoveColumns(#"Filtered Rows3",{"blogBody.Children.Children.Name"}),
#"Expand blogBody.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns5", "blogBody.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children", "blogBody.Children.Children.Children.Text"}),
#"Removed Columns6" = Table.RemoveColumns(#"Expand blogBody.Children.Children.Children",{"blogBody.Children.Children.Children.Text", "blogBody.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Name"}),
#"Expand blogBody.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns6", "blogBody.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Text"}),
#"Filtered Rows4" = Table.SelectRows(#"Expand blogBody.Children.Children.Children.Children", each ([blogBody.Children.Children.Children.Children.Name] = "TABLE")),
#"Removed Columns7" = Table.RemoveColumns(#"Filtered Rows4",{"blogBody.Children.Children.Children.Children.Text", "blogBody.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Name"}),
#"Expand blogBody.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns7", "blogBody.Children.Children.Children.Children.Children", {"Kind", "Name", "Children"}, {"blogBody.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children"}),
#"Removed Columns8" = Table.RemoveColumns(#"Expand blogBody.Children.Children.Children.Children.Children",{"blogBody.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Name"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns8", "blogBody.Children.Children.Children.Children.Children.Children", {"Children"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Expand blogBody.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Removed Columns9" = Table.RemoveColumns(#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children",{"blogBody.Children.Children.Children.Children.Children.Children.Children.Text", "blogBody.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Name"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns9", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Filtered Rows5" = Table.SelectRows(#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children", each ([blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Name] = "DIV")),
#"Removed Columns10" = Table.RemoveColumns(#"Filtered Rows5",{"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Text", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Name"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns10", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Removed Columns11" = Table.RemoveColumns(#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children",{"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns11", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Filtered Rows6" = Table.SelectRows(#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", each ([blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind] = "Element")),
#"Removed Columns12" = Table.RemoveColumns(#"Filtered Rows6",{"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns12", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Filtered Rows7" = Table.SelectRows(#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", each ([blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name] = "DIV")),
#"Removed Columns13" = Table.RemoveColumns(#"Filtered Rows7",{"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns13", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Removed Columns14" = Table.RemoveColumns(#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children",{"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns14", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Removed Columns15" = Table.RemoveColumns(#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children",{"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns15", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Filtered Rows8" = Table.SelectRows(#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", each ([blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind] = "Element")),
#"Removed Columns16" = Table.RemoveColumns(#"Filtered Rows8",{"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name"}),
#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns16", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text"}),
#"Removed Columns17" = Table.RemoveColumns(#"Expand blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children",{"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Name", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children", "blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Kind"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns17",{{"blogBody.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Children.Text", "rawText"}}),
#"Filtered Rows9" = Table.SelectRows(#"Renamed Columns3", each (Text.StartsWith([rawText], "January") or Text.StartsWith([rawText], "February") or Text.StartsWith([rawText], "March") or Text.StartsWith([rawText], "April") or Text.StartsWith([rawText], "May") or Text.StartsWith([rawText], "June") or Text.StartsWith([rawText], "July") or Text.StartsWith([rawText], "August") or Text.StartsWith([rawText], "September") or Text.StartsWith([rawText], "October") or Text.StartsWith([rawText], "November") or Text.StartsWith([rawText], "December")) and (Text.Contains([rawText], " 2")) and (Text.Contains([rawText], " (")) and (Text.Contains([rawText], ")"))),
#"Added Custom3" = Table.AddColumn(#"Filtered Rows9", "archiveMonth", each Text.Range([rawText],0,Text.PositionOf([rawText]," "))),
#"Added Custom1" = Table.AddColumn(#"Added Custom3", "archiveYear", each Text.Range([rawText],Text.PositionOf([rawText]," ")+1,4)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each MonthNumber([archiveMonth])),
#"Renamed Columns4" = Table.RenameColumns(#"Added Custom2",{{"Custom", "archiveMonthNumber"}}),
#"Added Custom4" = Table.AddColumn(#"Renamed Columns4", "archiveMonthUrl", each Text.Replace([blogUrl],"default",Text.Combine({"archive/",[archiveYear],"/",[archiveMonthNumber]}))),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "blogPostSummary", each GetBlogPostSummary([archiveMonthUrl])),
#"Expand blogPostSummary" = Table.ExpandTableColumn(#"Added Custom5", "blogPostSummary", {"blogPostSummary"}, {"blogPostSummary.blogPostSummary"}),
BlogPostSummary = Table.RenameColumns(#"Expand blogPostSummary",{{"blogPostSummary.blogPostSummary", "blogPostSummaryForTitle"}}),
#"Added Custom6" = Table.AddColumn(BlogPostSummary, "blogPostSummaryForComments", each [blogPostSummaryForTitle]),
#"Expand blogPostSummaryForTitle" = Table.ExpandTableColumn(#"Added Custom6", "blogPostSummaryForTitle", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForTitle.Kind", "blogPostSummaryForTitle.Name", "blogPostSummaryForTitle.Children", "blogPostSummaryForTitle.Text"}),
#"Filtered Rows10" = Table.SelectRows(#"Expand blogPostSummaryForTitle", each ([blogPostSummaryForTitle.Name] = "H4")),
#"Removed Columns18" = Table.RemoveColumns(#"Filtered Rows10",{"blogPostSummaryForTitle.Kind", "blogPostSummaryForTitle.Name", "blogPostSummaryForTitle.Text"}),
#"Expand blogPostSummaryForTitle.Children" = Table.ExpandTableColumn(#"Removed Columns18", "blogPostSummaryForTitle.Children", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForTitle.Children.Kind", "blogPostSummaryForTitle.Children.Name", "blogPostSummaryForTitle.Children.Children", "blogPostSummaryForTitle.Children.Text"}),
#"Filtered Rows11" = Table.SelectRows(#"Expand blogPostSummaryForTitle.Children", each ([blogPostSummaryForTitle.Children.Name] = "A")),
#"Removed Columns19" = Table.RemoveColumns(#"Filtered Rows11",{"blogPostSummaryForTitle.Children.Kind", "blogPostSummaryForTitle.Children.Name", "blogPostSummaryForTitle.Children.Text"}),
#"Expand blogPostSummaryForTitle.Children.Children" = Table.ExpandTableColumn(#"Removed Columns19", "blogPostSummaryForTitle.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForTitle.Children.Children.Kind", "blogPostSummaryForTitle.Children.Children.Name", "blogPostSummaryForTitle.Children.Children.Children", "blogPostSummaryForTitle.Children.Children.Text"}),
#"Removed Columns20" = Table.RemoveColumns(#"Expand blogPostSummaryForTitle.Children.Children",{"blogPostSummaryForTitle.Children.Children.Kind", "blogPostSummaryForTitle.Children.Children.Name", "blogPostSummaryForTitle.Children.Children.Children"}),
#"Renamed Columns5" = Table.RenameColumns(#"Removed Columns20",{{"blogPostSummaryForTitle.Children.Children.Text", "blogPostTitle"}}),
#"Expand blogPostSummaryForComments" = Table.ExpandTableColumn(#"Renamed Columns5", "blogPostSummaryForComments", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForComments.Kind", "blogPostSummaryForComments.Name", "blogPostSummaryForComments.Children", "blogPostSummaryForComments.Text"}),
#"Filtered Rows12" = Table.SelectRows(#"Expand blogPostSummaryForComments", each ([blogPostSummaryForComments.Name] = "TABLE")),
#"Removed Columns21" = Table.RemoveColumns(#"Filtered Rows12",{"blogPostSummaryForComments.Kind", "blogPostSummaryForComments.Name", "blogPostSummaryForComments.Text"}),
#"Expand blogPostSummaryForComments.Children" = Table.ExpandTableColumn(#"Removed Columns21", "blogPostSummaryForComments.Children", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForComments.Children.Kind", "blogPostSummaryForComments.Children.Name", "blogPostSummaryForComments.Children.Children", "blogPostSummaryForComments.Children.Text"}),
#"Removed Columns22" = Table.RemoveColumns(#"Expand blogPostSummaryForComments.Children",{"blogPostSummaryForComments.Children.Kind", "blogPostSummaryForComments.Children.Name", "blogPostSummaryForComments.Children.Text"}),
#"Expand blogPostSummaryForComments.Children.Children" = Table.ExpandTableColumn(#"Removed Columns22", "blogPostSummaryForComments.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForComments.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Name", "blogPostSummaryForComments.Children.Children.Children", "blogPostSummaryForComments.Children.Children.Text"}),
#"Removed Columns23" = Table.RemoveColumns(#"Expand blogPostSummaryForComments.Children.Children",{"blogPostSummaryForComments.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Name", "blogPostSummaryForComments.Children.Children.Text"}),
#"Expand blogPostSummaryForComments.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns23", "blogPostSummaryForComments.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForComments.Children.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Children.Name", "blogPostSummaryForComments.Children.Children.Children.Children", "blogPostSummaryForComments.Children.Children.Children.Text"}),
#"Removed Columns24" = Table.RemoveColumns(#"Expand blogPostSummaryForComments.Children.Children.Children",{"blogPostSummaryForComments.Children.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Children.Name", "blogPostSummaryForComments.Children.Children.Children.Text"}),
#"Expand blogPostSummaryForComments.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns24", "blogPostSummaryForComments.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForComments.Children.Children.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Children.Children.Name", "blogPostSummaryForComments.Children.Children.Children.Children.Children", "blogPostSummaryForComments.Children.Children.Children.Children.Text"}),
#"Removed Columns25" = Table.RemoveColumns(#"Expand blogPostSummaryForComments.Children.Children.Children.Children",{"blogPostSummaryForComments.Children.Children.Children.Children.Text", "blogPostSummaryForComments.Children.Children.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Children.Children.Name"}),
#"Expand blogPostSummaryForComments.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns25", "blogPostSummaryForComments.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForComments.Children.Children.Children.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Name", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Children", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Text"}),
#"Filtered Rows13" = Table.SelectRows(#"Expand blogPostSummaryForComments.Children.Children.Children.Children.Children", each ([blogPostSummaryForComments.Children.Children.Children.Children.Children.Name] = "A")),
#"Removed Columns26" = Table.RemoveColumns(#"Filtered Rows13",{"blogPostSummaryForComments.Children.Children.Children.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Name", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Text"}),
#"Expand blogPostSummaryForComments.Children.Children.Children.Children.Children.Children" = Table.ExpandTableColumn(#"Removed Columns26", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Children", {"Kind", "Name", "Children", "Text"}, {"blogPostSummaryForComments.Children.Children.Children.Children.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Children.Name", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Children.Children", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Children.Text"}),
#"Removed Columns27" = Table.RemoveColumns(#"Expand blogPostSummaryForComments.Children.Children.Children.Children.Children.Children",{"blogPostSummaryForComments.Children.Children.Children.Children.Children.Children.Kind", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Children.Name", "blogPostSummaryForComments.Children.Children.Children.Children.Children.Children.Children"}),
#"Filtered Rows14" = Table.SelectRows(#"Removed Columns27", each Text.EndsWith([blogPostSummaryForComments.Children.Children.Children.Children.Children.Children.Text], "Comments")),
#"Renamed Columns6" = Table.RenameColumns(#"Filtered Rows14",{{"blogPostSummaryForComments.Children.Children.Children.Children.Children.Children.Text", "commentsText"}}),
#"Added Custom7" = Table.TransformColumnTypes(Table.AddColumn(#"Renamed Columns6", "commentTally", each Text.Replace([commentsText], " Comments","")),{{"commentTally", Int64.Type}}),
#"Added Custom9" = Table.AddColumn(#"Added Custom7", "blogPostTally", each 1),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom9",{{"blogPostTally", Int64.Type}}),
#"Removed Columns28" = Table.RemoveColumns(#"Changed Type2",{"rawText", "commentsText"}),
#"Removed Columns29" = Table.RemoveColumns(#"Removed Columns28",{"archiveMonth", "archiveYear", "archiveMonthNumber", "archiveMonthUrl"})
in
#"Removed Columns29"
let
GetBlogSummary = (archivePage as text) =>
let
Source = Web.Page(Web.Contents(archivePage)),
#"Expand Data" = Table.ExpandTableColumn(Source, "Data", {"Children", "Kind", "Name", "Text"}, {"Data.Children", "Data.Kind", "Data.Name", "Data.Text"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expand Data",{"Data.Children"}),
#"Expand Data.Children" = Table.ExpandTableColumn(#"Removed Other Columns", "Data.Children", {"Kind", "Name", "Children", "Text"}, {"Data.Children.Kind", "Data.Children.Name", "Data.Children.Children", "Data.Children.Text"}),
#"Data Children Children" = #"Expand Data.Children"{1}[Data.Children.Children],
Children = #"Data Children Children"{0}[Children],
Children1 = Children{4}[Children],
Children2 = Children1{0}[Children],
Children3 = Children2{1}[Children],
Children4 = Children3{8}[Children],
Children5 = Children4{0}[Children],
Children6 = Children5{0}[Children],
Children7 = Children6{1}[Children],
Children8 = Children7{0}[Children],
Children9 = Children8{0}[Children],
Children10 = Children9{1}[Children],
Children11 = Children10{0}[Children],
Children12 = Children11{0}[Children],
Children13 = Children12{2}[Children],
Children14 = Children13{1}[Children],
Children15 = Table.SelectColumns(Children14,{"Children"}),
BlogPostSummary = Table.RenameColumns(#"Children15",{{"Children", "blogPostSummary"}})
in
BlogPostSummary
in
GetBlogSummary
let
MonthNumber = (MonthName as text) =>
let
CaseValues = {
{"January", "01"},
{"February", "02"},
{"March", "03"},
{"April", "04"},
{"May", "05"},
{"June", "06"},
{"July", "07"},
{"August", "08"},
{"September", "09"},
{"October", "10"},
{"November", "11"},
{"December", "12"},
{MonthName, ""}
},
SimpleCase = List.First(List.Select(CaseValues, each _{0}=MonthName)){1}
in
SimpleCase
in
MonthNumber
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment