Great Apps News

This was the picture I have seen today, I knew that Apps are part of PowerBI, I thought I was ready and knew everything about it but seeing it this morning in feed when opening PowerBI made it official.  Isn’t it exciting?

So I thought it must be something connecting PowerApps and PowerBI but apparently it is a confusing term. From this link we can connect to other applications in our organization (86 options for the PowerBI related apps).

This is also not the connection to PowerApps, this is done through Flow and Microsoft has 121 pages document describing the how. () You can also visit our SharePoint user group or PowerBI user group in Vancouver we talk about this connection in many of the sessions.

There is an Application insights option to be used as part of this platform any application your organization has already got, similar to the get data menu options; but how can I create and app?

I have recently created a report and a dashboard that has the result of the BC elections.

After publishing the report and the data set to PowerBI, I have created a Dashboard based on the report and created an App.

The result is a Shipping App that is part of the Shipping Group.

The Apps is currently a Read only replacement of the former content packs. For now, I cannot move the app or share it with another group, I cannot do much or customize the already created visualizations. This will probably change very soon.

What I can do?

  1. I can see all the artifacts in the App:
    1. the Dashboard,
    2. the Report
    3. the Data Set
  2. I can filter and drill down data in the dashboard
  3. I can filter the data in the report and choose the displayed fields from the existing once.

What is the Business need?

If I have users that I want them to access only this and not play too much with the visualizations or the data.

  Here is the report view with the filters.

On the top (the red ribbon color is customization I can see the number of dashboards, reports and data sets that are part of the app.)

The right navigation is for the filtering option

The canvas is static and you cannot edit the existing report (Permanent or temporary?)

The report can be embedded in SharePoint group same as any other report, I assume that the apps will be visible in the SP gallery soon, I will keep posted if the apps are going to be available by group or cross organization. I would also add an option to publish in the App Catalog, that would have given me the option to link it here. Very excited to see what’s next and write about it here.

 

 

 

 

Election in BC Part 1 – Preparing the data

Yesterday was May 9th that means the election day!

There were elections and I felt like our beloved province of British Colombia didn’t get the necessary attention and doesn’t have it’s own data visualization solution! There was a small one in the Vancouver sun: but it is a custom based solution by their talented Graphic designer: Lucas Timmons (GitHub, Twitter: @lucastimmons) not a PowerBI or Tableau!  Not to worry BC Yana to the rescue!

The data sources are 2 pages with the elections results:

1st data set: Results split by candidates: http://electionsbcenr.blob.core.windows.net/electionsbcenr/GE-2017-05-09_Candidate.html

2nd data source: Results split by parties:

http://electionsbcenr.blob.core.windows.net/electionsbcenr/GE-2017-05-09_Party.html

Since Lucas did the map data visualization (I don’t like the colors but I will not be doing the same).

So What shall we do?

  • I have opened PowerBI Desktop
  • added a data source of the type Web

1st data set: Results split by candidates:

  • Added the link to the first HTML page and in the selection dialog chose: Table 0. PowerBI recognized the table on the page and uploaded it.

Data manipulation I have used: Complete missing lines in the 1st data set

The data set items look like this and I need to fill all the blanks to get the community name:

Electoral District Candidate’s Ballot Name Affiliation Total Valid Votes % of Popular Vote
Abbotsford-Mission Dan Cameron Christian Heritage Party of B.C. 607 2.60%
Andrew Murray Christie BC NDP 6,754 28.89%
Simon John Gibson BC Liberal Party 12,080 51.67%
Jennifer Holmes BC Green Party 3,940 16.85%

(referenced from the PowerBI community site)

What shall I do? how to fill the missing data? so the obvious choice might be export everything to Excel or even just copy to Excel and duplicate the result. Let us not despair, this is exactly what DAX & M are for!

I have added a new custom column with the following function written to add it in DAX:

= Table.AddColumn(#”Changed Type”, “Custom”, each if [Electoral District]=”” then null else [Electoral District])

Than I have used the Fill option, from the Transform ribbon tab to fill down all the Null cells. The problem that accord when the Fill down didn’t work in the original column was that those cells did not have the null value to begin with, means they are not empty and cannot be filled according to the definitions.

In the View Ribbon tab, I can see all the data manipulation I have completed so far using M:

let
Source = Web.Page(Web.Contents(“http://electionsbcenr.blob.core.windows.net/electionsbcenr/GE-2017-05-09_Candidate.html”)),
Data0 = Source{0}[Data],
#”Changed Type” = Table.TransformColumnTypes(Data0,{{“Electoral District”, type text}, {“Candidate’s Ballot Name”, type text}, {“Affiliation”, type text}, {“Total Valid Votes”, type number}, {“% of Popular Vote”, Percentage.Type}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Custom”, each if [Electoral District]=”” then null else [Electoral District]),
#”Filled Down” = Table.FillDown(#”Added Custom”,{“Custom”})
in
#”Filled Down”

Data manipulation 2-

I have also removed the total 100% ballot box number as the bottom value for each community. This value we can get later in the 2nd data set by party and connect them by community. Or simply add a measure to calculate it.

I haven’t used code this time, only the existing PowerBI options.

  1. Transform ribbon tab->Split the column [Electoral District]
  2. Used the custom split with the custom value “of” (common for all the ballot boxes numbers there)
  3. Got 2 columns when in the target columns had “null” value for all the rows without the ballot counting
  4. Used the top row filter to simply filter out all the rows that didn’t have null
  5. In PowerBI, now that I don’t need the extra data manipulation column, I just got rid of it 🙂 delete column it is.

The current M code for the whole operation looks like this: (It’s different for a data source that didn’t have to be changed from HTML, the references might contain other commands for different data source data types)

let
Source = Web.Page(Web.Contents(“http://electionsbcenr.blob.core.windows.net/electionsbcenr/GE-2017-05-09_Candidate.html”)),
Data0 = Source{0}[Data],
#”Changed Type” = Table.TransformColumnTypes(Data0,{{“Electoral District”, type text}, {“Candidate’s Ballot Name”, type text}, {“Affiliation”, type text}, {“Total Valid Votes”, type number}, {“% of Popular Vote”, Percentage.Type}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Custom”, each if [Electoral District]=”” then null else [Electoral District]),
#”Filled Down” = Table.FillDown(#”Added Custom”,{“Custom”}),
#”Renamed Columns” = Table.RenameColumns(#”Filled Down”,{{“Custom”, “Full Electoral District”}}),
#”Split Column by Delimiter” = Table.SplitColumn(#”Renamed Columns”, “Electoral District”, Splitter.SplitTextByDelimiter(“of”, QuoteStyle.Csv), {“Electoral District.1”, “Electoral District.2″}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Electoral District.1”, type text}, {“Electoral District.2″, type text}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type1″, each ([Electoral District.2] = null)),
#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{“Electoral District.2″})
in
#”Removed Columns”

The data set now look like this:

2nd data source: Results split by parties:

 

(referenced from the PowerBI community site)

  • I did the same entering the URL of the results by party
  • Entered to edit the data and split the column with the ballet boxes. I only need their number not the real time count since the election are over.

The data manipulation in the query mode I have used: Split cells,
the split value was: “of” since the values are: “35 of 35”. I ended up with 2 columns containing the same number. so I deleted one of them.

  • I have made sure that all the columns are from the type Decimal number, since I really don’t want to upload a column containing numbers as a text column and wonder why things keep not working? 🙂
  • Next I have uploaded the data to the data set as well.

Now I have 2 Tables containing the data – so easy now the visualization part begins!

(in part 2)

 

Creating my story with Data

So I was looking to make my resume stand out while presenting my skills with the new and shiny PowerBI. (my powerBI page in my site)

I was inspired by an article, Business Insider has created about Marissa Mayers resume.

So I have continued, just like with any other product, using my Business Analysis skills:

Defining the the Business need​:

  • Who is the audience?
    • Potential employer/customer
    • People I am looking to impress
  • Why I want them to see it? ​
    • To show case my skills with PowerBI
    • Look into the details of my resume without getting bored
  • What do I want to  show them? ​
    • How can I tell a story using data

Now that I know what is going to be part of my story with data, I have started to think about the technical aspect.

Technical requirements  ​

  • Real time update? Custom visuals? Sharing? Mobile? ​

​The real time update won’t be necessary, once a month I will update my current work status and the dashboard can be updated within 24 hours. My subscription (pro) allows up to 8 scheduled updates a day, with an hour minimum intervals. I can also manually refresh and republish the dashboard if this is a live demo. The reports were created for my web page, which is built using HTML CSS and some JS for the fun parts. The design is responsive but I will not create separate mobile version of the data visualizations. The out of the box PowerBI versions seemed too look fine in the mobile browser. Since the target audience is going to watch from the browser and not from the PowerBI mobile app as this is not a link to the dashboard but an embedded report.

Designing the solution

What do I need?

  1. Data Source to create a data set –
    • I have used first a google docs table but it had one problem, it was not saved as a table and I needed to save it again from scratch.
    • I have used a SharePoint list, but discovered that this cannot yet be a direct data source (this was done just a month before it became an option 🙂 )
    • I have used an Excel file, that is saved in a SharePoint library and is accessible publicly. (it’s called One Drive bc I have first saved it there and than moved to the SP library. It works from One drive the same way) I have connected this as a data source in PowerBI, after saving the data in the Excel as a table which is very important to make the spreadsheet a valid data source.
      The data source contains a line for each month since my graduation and starting my career, September 2005 till today (March 2017). In each line, I have shown my work location, the field, technologies I have been working with, company I have worked for and off course the job title.
  2. Report/Reports
    Based on the data-set I have created a new report:
    2.1 dragged the first data visualization I have chosen, the map, checked the columns from the table indicating location and
    2.2 Chose the next visualization the Treemap, chose the Group as the Location city, the Values as Count of Workplace (which will count the number of times the specific workplace is displayed in the rows)
    2.3 Dragged the bar-chart visual to have the information regarding my experience in each industry type to be presented.
    The Treemap visualization filter selection can be seen here:
  3. Publish option –

I have saved the report in my PowerBI account, that means it was uploaded to my storage, the PowerBI API library, on my Office365 account. After which I have chosen in the menu File-> Publish to web
PowerBI have opened a wizard, where I could choose the option of embedding in my WebSite. The result was the following: 

This is what I have pasted in the HTML code of my WebSite:

<div class=”container”>
<div class=”row”>
<iframe width=”800″ height=”600″ src=”https://app.powerbi.com/view?r=eyJrIjoiZTY4Y2M4NTktMjZmMS00ZWEwLTkyZmQtMDliMmMyZjRjYmVjIiwidCI6IjAyMTJmZDNkLWQ1ZGYtNGVkNy05NzYyLTQ5ZGRmZDQ1ZGM1MCIsImMiOjZ9″ frameborder=”0″ allowFullScreen=”true”></iframe>
</div>
<div class=”row”>
<div class=”col s12″>
<iframe width=”800″ height=”600″ src=”https://app.powerbi.com/view?r=eyJrIjoiN2NjZDIzMjUtNThjNC00YTUzLThhNDgtMDBlMmM2ZGYzNzVkIiwidCI6IjAyMTJmZDNkLWQ1ZGYtNGVkNy05NzYyLTQ5ZGRmZDQ1ZGM1MCIsImMiOjZ9″ frameborder=”0″ allowfullscreen=”true”></iframe>
</div>
<div class=”col s12″>
<h2 class=”text_h2″> MY PowerBI Twitter Feed </h2>
<a class=”twitter-timeline” href=”https://twitter.com/search?q=%23PowerBI%20from%3Ayana_berkovich” data-widget-id=”843961672100274176″>Tweets about #PowerBI from:yana_berkovich</a>
<script>!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?’http’:’https’;if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+”://platform.twitter.com/widgets.js”;fjs.parentNode.insertBefore(js,fjs);}}(document,”script”,”twitter-wjs”);</script>
</div>
</div>
</div>

The last div is for the Twitter feed related to PowerBI that I have created, just to add this is just for fun and actually works! 🙂

Testing ​

  • Hi there, have you seen my page yet? (5 AM Israel time is 7 PM in Vancouver…)​ I have asked people with different browsers and used the chrome different sizing option to test in different screen sizes. (There are still some problems with the top links in my site but that’s not part of the PowerBI topic…so next post)

Requirements gathering featuring myself as the business user is a very interesting experience; Documentation is still important! (Otherwise it takes you forwever to find the right report.​

The page link: http://yanaberkovich.com/powerBI.html ​

The link to the lightning session