Just want to introduce a sample Power BI report with some Power Query use.
Description:
- A single Power BI file "PassSummit_Schedule.pbix" (can be downloaded by click);- It is a little and very simple Power BI report, which only provides browsing and selecting capabilities over PASS Summit 2019 schedule;
- The report is tied to PASS web site schedule page: https://www.pass.org/summit/2019/Learn/Schedule.aspx;
- The report demonstrates the basic Power Query and Power BI capabilities;
- The report can be viewed and used via Power BI Desktop Application or uploaded to your own Power BI Azure portal.
Functionality:
- You can browse through all PASS Summit 2019 sessions sorting and filtering them by day, time, speaker, level, and room;
- You can build your own schedule and get printable version of it.
Here is how it looks like:
Internals:
The report references PASS Summit schedule page: https://www.pass.org/summit/2019/Learn/Schedule.aspx as a data source using following Power Query:| let Source = Web.Page(Web.Contents("https://www.pass.org/summit/2019/Learn/Schedule.aspx")), Data1 = Table.AddColumn(Table.AddIndexColumn(Source{5}[Data], "Index", 0, 1), "PassSummitDay", each "Day 1 - Monday"), Data2 = Table.AddColumn(Table.AddIndexColumn(Source{6}[Data], "Index", 0, 1), "PassSummitDay", each "Day 2 - Tuesday"), Data3 = Table.AddColumn(Table.AddIndexColumn(Source{7}[Data], "Index", 0, 1), "PassSummitDay", each "Day 3 - Wednesday"), Data4 = Table.AddColumn(Table.AddIndexColumn(Source{8}[Data], "Index", 0, 1), "PassSummitDay", each "Day 4 - Thursday"), Data5 = Table.AddColumn(Table.AddIndexColumn(Source{9}[Data], "Index", 0, 1), "PassSummitDay", each "Day 5 - Friday"), Data59 = Table.Combine({Data1,Data2,Data3,Data4,Data5}), #"Changed Type" = Table.TransformColumnTypes(Data59,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}}), #"Combined columns" = Table.Distinct( Table.Combine({ Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column2","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 0), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column3","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 1), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column4","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 2), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column5","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 3), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column6","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 4), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column7","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 5), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column8","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 6), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column9","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 7), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column10","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 8), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column11","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 9), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column12","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 10), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column13","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 11), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column14","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 12), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column15","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 13), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column16","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 14), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column17","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 15), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column18","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 16), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column19","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 17), Table.AddColumn(Table.SelectColumns(Table.RenameColumns(#"Changed Type", {{"Column1","Time"},{"Column20","Session"}}),{"PassSummitDay", "Time", "Session", "Index"}), "Index2", each 18)})), #"Sorted Rows" = Table.Sort(#"Combined columns",{{"PassSummitDay", Order.Ascending}, {"Index2", Order.Ascending}, {"Index", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index1", 0, 1), #"Filtered Rows1" = Table.SelectRows(#"Added Index", each ([Time] <> "Room")), #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Time] = "Room")), #"Filtered Rows2" = Table.AddColumn(#"Filtered Rows", "Index3", each [Index1]+1), #"Merged Queries" = Table.NestedJoin(#"Filtered Rows1",{"Index1"},#"Filtered Rows2",{"Index3"},"Filtered Rows1",JoinKind.LeftOuter), #"Expanded Filtered Rows1" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows1", {"Session"}, {"Room"}), #"Grouped Rows" = Table.Group(#"Expanded Filtered Rows1", {"PassSummitDay", "Time", "Room", "Session", "Index"}, {{"Index3", each List.Max([Index2]), type number}}), #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"PassSummitDay", Order.Ascending}, {"Index", Order.Ascending}, {"Index3", Order.Ascending}}), #"Added Index0" = Table.AddIndexColumn(#"Sorted Rows1", "Index0", 0, 1), #"Removed Columns" = Table.RemoveColumns(#"Added Index0", {"Index","Index3"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Session", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, false), {"Session Topic", "Speaker"}), #"Changed Type0" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Session Topic", type text}, {"Speaker", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type0", "Speaker", Splitter.SplitTextByEachDelimiter({"#(cr)#(lf)"}, QuoteStyle.Csv, true), {"Session Speaker", "Session Level"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Session Speaker", type text}, {"Session Level", type text}}), #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1","#(cr)#(lf)","",Replacer.ReplaceText,{"Session Speaker"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","#(cr)#(lf)","",Replacer.ReplaceText,{"Session Topic"}), #"Trimmed Text" = Table.TransformColumns(#"Replaced Value2",{{"Session Speaker", Text.Trim}}), #"Replaced Value3" = Table.ReplaceValue(#"Trimmed Text",null,"",Replacer.ReplaceValue,{"Session Topic", "Session Speaker","Session Level", "Room"}), #"Filtered Rows3" = Table.SelectRows(#"Replaced Value3", each ([Session Topic] <> "") and not Text.Contains([Time], "* ")), #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows3", "Show", each if [Room] = "" then "Breaks" else "Sessions") in #"Added Conditional Column" |
Even though query looks quite a large, but it is just a set of single steps.
For the front end the report uses three pages:
- Main Schedule - Use of Sorting & Filtering Power BI features;
- Build My Schedule - Here you can select sessions you'd like to attend;
- My Schedule - Full report of selected sessions.
Disclaimer:
Unfortunately the report is not looking at the original data source and tided only to the web page. That means that any formatting changes on the schedule page will break the Power BI report, but I hope there will be non changes until the PASS Summit other than filling the gaps for "To Be Announced" items, which should be handled correctly.In case report breaks, please let me know and I'll fix it.
No comments:
Post a Comment