Final build and function – WW-02-09

by

When you look to move your Power BI content forward, there are many “rules” we have to consider and remember. The principle is that requirements, plans, and delivery can all be “different” yet still be successful. The most critical person in “I don’t like…” will be you as the developer. So remember to go back and ask, “Is this meeting the requirement”?

YouTube player

Going “live” with something is never a “We’re now perfect, let’s launch” – I remember a stat about Windows when someone was complaining to a Developer about all the bugs and vulnerabilities that come up, the response they got was along the lines of “We have to release a product to market to pay the wages, to debug and remediate every line of code fully would take more than 1000 years”. That is true – all be it on a smaller scale – about your Power BI content. Making that perfect report is possible, but not if you want to meet any meaningful deadline.

Plan your deployment and think of using many small reports (secondary reports are your friend, remember). For each small report, consider the Audience and their requirements. In terms of our parkrun report, we are building a single small report for a specific group of people (parkrunners). This group has particular requirements.

This week we also looked at what is involved with adding a new dataflow – once you’ve done that, you need to remember to add it into your *.pbix file and then add it into your base Appended Query. This time I have improved on what was happening with the first set to develop a function.

let
  Source = (Event as text) => let
  Eventnumber = "36",
  Source = Web.Contents("https://www.parkrun.org.uk/",
 [ 
     RelativePath="jesmonddene/results/weeklyresults/",
     Query=[runSeqNumber= Event] 
     ]),
  #"Extracted Table From Html" = Html.Table(Source, {
        {"Link", "a", each [Attributes][href]},
        {"Name", ".Results-table-td\-\-name .compact *"},
        {"Gender", ".Results-table-td\-\-gender .compact"},
        {"GP_Full", ".Results-table-td\-\-gender .detailed"},
        {"Category", ".Results-table-td\-\-ageGroup A"},
        {"Age Grade", ".Results-table-td\-\-ageGroup .detailed"},
        {"FinishTime", ".Results-table-td\-\-time .compact"},
        {"PB", ".Results-table-td\-\-time SPAN"},
        {"Club", ".Results-table-club A"}, 
	      {"r10", ".milestone-r10"},
            {"r25", ".milestone-r25"},
            {"r50", ".milestone-r50"},
            {"r100", ".milestone-r100"},
            {"r250", ".milestone-r250"},
	      {"r500", ".milestone-r500"},
            {"v25", ".milestone-v25"},
            {"v50", ".milestone-v50"},
            {"v100", ".milestone-v100"},
            {"v250", ".milestone-v250"},
		{"v500", ".milestone-v500"}},
      [RowSelector=".Results-table-td\-\-name .compact *"]),
  #"Split column by delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Extracted Table From Html", {{"Link", type text}}), "Link", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Link.1", "parkrunID"}),
  #"Added Finish Positions" = Table.AddIndexColumn(#"Split column by delimiter", "FinishPosition", 1, 1, Int64.Type),
  #"Replaced value" = Table.ReplaceValue(#"Added Finish Positions", " age grade", "", Replacer.ReplaceText, {"Age Grade"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Replaced value", {{"Link.1", type text}, {"parkrunID", Int64.Type}}),
  #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Changed column type", "Time", each if  Text.Length([FinishTime]) =5 then "00:" & [FinishTime] else [FinishTime]), {{"Time", type time}}),
  #"Removed columns" = Table.RemoveColumns(#"Added custom", {"Link.1", "FinishTime"}),
  #"Changed column type 1" = Table.TransformColumnTypes(#"Removed columns", {{"Age Grade", Percentage.Type}})
in
  #"Changed column type 1"
in
  Source

In 02-10, we’ll discuss the details of the beginning of this function. To write a Function that pulls from different URL’s you have to use more than just the URL. This is so that the “connection” does not change because, for security, the query will not execute if the URL is changing. I promise it all makes sense.

Leave a Reply