Created
March 25, 2015 19:08
-
-
Save jamiekt/bac21f6169a9853dcef5 to your computer and use it in GitHub Desktop.
M code to obtain SQLBlog stats
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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