Today, I’ll give you a fantastic tip that will make your analysis improve.
You’ll now be able to show rows with no data.
PowerBI does not show rows without data by default. But it is pretty easy to see all rows, even without data.
That’s because all Joins between tables (Dimension Tables & Fact Tables) in Power BI are Left Joins.
Check out this simple tip to view all products or categories etc. from any dimension, without missing any master data rows.
For the transactional data, only the data where rows exist in Fact tables will appear with Values. But for Dimension values without any matching transaction, you’ll see a blank row. This is pretty useful as now you have a complete view of the data, and can see where there is no data.
Hope you like this simple & quick Tip on Power BI Visualization.
Learn Power BI From Practicing Professionals in Singapore
Intellisoft Systems conducts PowerBI training in Singapore each month. Do attend our hands-on practical training to learn Power BI from the beginning, and be able to analyze and visualize data easily with Microsoft tools.
Written & Narrated by: Vinai Prakash, Founder & Master Trainer, Intellisoft Systems
Vinai conducts the Microsoft Power BI trainings in Singapore. His Power BI courses are extremely popular, fun and easy to learn for beginners and experienced professionals alike.
Join Vinai in his next Power BI training course at Intellisoft. You won’t regret it!
Been hearing a lot about Power Query, Power Pivot or Power BI lately?
Has your management or HQ asked you to quickly learn Power BI?
If the answer to any of the above questions is Yes, then it is time you learned somethings about the latest offerings from Microsoft for Business Intelligence.
After all, Microsoft is way ahead of the competition in terms of Vision, Strategy & Speed of Execution in the space of Business Intelligence. See the latest Gartner Research on Business Intelligence for yourself.
Microsoft is now miles ahead of Tableau or Qlik.
Microsoft-Power-BI Leader in 2019 – Gartner Research
No wonder companies are ditching such software and migrating their Dashboards & entire reporting environment to Microsoft Power BI.
Current State of Enterprise Reporting
Most likely your current state of Corporate Reporting is confined to Analysis in Excel, Conversion of the analysis results to a Line or Bar Chart, and then Pasting the charts into PowerPoint for presentation to the management & clients. This presents a static view of the data, shown in board rooms all over the world.
However, the key thing lacking in such reports is the interactivity. Suppose your customer suddenly asks you to compare the last quarter with the same quarter a couple of years back. While you might have the data, you don’t have the chart ready-made, right now. Chances are high that you’d have to apologise and promise to show them the requested report or chart in a subsequent meeting.
But Business can’t wait! By the time you show them the report next month, it may be useless, and people would have even forgotten about it too.
Plus, sharing data with your users is a big problem. You’d have to send huge Excel files, that take time to load, and are full of VLookups to different prices and master codes. If someone were to tinker and make a change in a place, chances are that the whole thing might collapse, and render the reporting useless.
Additionally, Reports don’t refresh automatically each month. Someone needs to load the next month’s data, and refresh the reports manually, month after month. This wastes so much corporate time & resources.
The ability to show any data, from any month, quarter or year, on the fly, can help the business answer any question they may have, and react faster!
And it would work wonders if the reports could refresh automatically, month after month, without anyone’s intervention.
Enter Power BI Suite of Products…
Microsoft Power BI Suite Traning in Singapore
What is PowerBI Suite of Products?
Power BI is a brand new product from Microsoft. It was launched in 2015, and in less than 5 years, it has gained supremacy in the Business Intelligence space.
Power BI enables the common users to build stunning reports, dashboards, and make them available to all users, without having to download any expensive software. Users can consume the reports anytime, anywhere, even with just a phone or on an iPad, on any browser.
The reports are a visual treat, and make playing with data a breeze. It is extremely user-friendly and has hardly any learning curve.
If someone can use a web page, they can consume a dashboard done in Power BI and analyze data to their heart’s content – slice it, dice it, export it, print it, compare it with another month, year on any business segment, any category, any zone.
The Power BI Desktop is free to download. It can fetch data from over 70 different sources, including Excel, any SQL or native Database, Web, SalesForce, SAP, Azure, Google Analytics, Mailchimp, ZenDesk, Twilio, SurveyMonkey and several other cloud services. Power BI is able to clean the data, process it, and get it ready for consumption, all in a simple, easy to use software.
PowerBI Desktop Download
Everything you need is in-built, ready to use.
There are 4 major components – Power Query, Power Pivot, Power BI & the Power BI Online Services.
They help you to Get Data, Analyze Data, Visualize Data, and then Share Data with your users. Let’s delve into these components a bit deeper…
What is Power Query?
Power Query allows us to bring in data from almost any source, clean it, fill empty values, replace nulls, remove empty rows, and do Vlookup type operations into other tables to pick any reference values, all without writing a single piece of VBA code, or any formula.
Microsoft PowerQuery Training in Singapore
Everything is done through the elaborate options, buttons, settings and features of Power Query.
I was able to clean very dirty data files in just 12 minutes, which would have taken me at least 3-4 hours to do manually in Excel.
And I don’t have to do it again! Next week, when the new data file comes from the ERP system, I just have to drop it in the correct folder, and it will be cleaned up automatically.
PowerQuery was released as an Add-On component in Excel 2010 & Excel 2013, but it is now embedded into Excel 2016, 2019 and Office 365. No need to install or enable anything.
It is ready, enabled by default, and available at a click of the button, already existing in the Microsoft Excel DATA tab, as well as in Power BI Desktop.
Have fun with it… it is an absolute delight to load and clean data using Power Query. Once the data is loaded into Power BI, we then have to learn how to create a data model in Power BI.
What is Power Pivot?
PowerPivot is the engine that powers the data visualizations in Power BI. It runs in the background in Power BI, and as an add-on within Microsoft Excel.
ower
Power Pivot & Components
Loading Millions of Rows, Fast!
Each Excel worksheet has a limit of just over a million rows. 1,048,576 Rows to be exact. This is a logical limit. However, most of the time if I only load 500,000 rows of transactional data, the Excel file becomes quite large and takes forever to open.
Plus, multiple Vlookups, complex Formulas etc. can slow things down, and Excel becomes unresponsive for long periods as we make changes on large worksheets and workbooks.
Enter Power Pivot, which is an Add-on to Excel (Yes, it is still available as an Add-On on Office 2013, 2016, 2019 & Office 365).
With Power Pivot, this limit of just 1 million rows is easily eliminated. Now we can load millions of rows, and the file size does not grow considerably. Plus the Excel files are quite responsive and able to handle things quite fast.
This is because PowerPivot does not store the data in the traditional Excel way. It uses the Vertipaq Columnar Database, which compresses the data, and loads what is needed for any calculations in the RAM only.
The speed is blazing fast and allows you to work with Excel freely. Plus, it removes all the limitations that came with Excel Pivot Tables.
Now we can extend the normal pivots by loading data from multiple Tables, Multiple Files, Multiple Sources, and combine them, merge them, mash them up and report using a Data Model, which can have relationships with the different entities.
The ability to load from multiple sources, and create pivot table reports that use Big Data (1 Million Plus… usually 40-60 million rows is not a problem), and is still quite fast.
I haven’t seen Excel ever hang or blue screen on me even with this huge sized data, which is what I use most of the time. It is like working off our Corporate Oracle Database, which has over 500 Million+ rows of transactions and is over 80TB in size.
Data Model View
I can view the entire data model visually, and see the relationships… something that I couldn’t do in several other databases or reporting software. This allows you to see the tables and their relationship with other tables easily. A visual data model shows the relationships clearly.
Data
Data Modeling in Power Pivot
DAX – A New Language For Writing Amazing Formulas For Visualization
With PowerPivot, Microsoft has introduced a plethora of new formulas, a completely new language of writing formulas – called DAX (Data Analysis Expressions).
DAX Expressions in Power BI & Power Pivot – Get Trained in Singapore
I simply love writing DAX to calculate things which would have taken me complex formulas to compute, with a lot of helper columns, tables and worksheets.
Things like measuring Revenue from the same period last year, last quarter and available in a simple function. Counting Distinct Rows, Calculating things over multiple tables, with multiple complex conditions is handled so seamlessly and easily, that I am amazed.
I used to be a die-hard SQL fan, being able to extract any data from any database using SQL, but with DAX, it puts my SQL skills to shame. The DAX calculations functions are aplenty and make any calculation a matter of a few minutes to write.
However, learning DAX does take time. There’s Row Context, Filter Context, and the ability to alter context on the fly take a while to understand. Newbies often get stumped in understanding these concepts and it does take time to get a good handle on writing good DAX.
Even though learning DAX is complex and takes time to master, the effort is simply worth it.
I never regretted the time I spent in learning and writing good quality DAX. It has allowed me to calculate complex things for myself and my clients.
In fact, almost all of my consulting time is spent in helping clients read, understand or write complex DAX measures. They love it, and I love teaching it too.
Learn Complex DAX Measures in Microsoft Power BI
Once the DAX functions are written, it is time to visualize the data. This can be done in a normal Pivot Table in Excel, but I prefer to visualize this in Power BI – which has a number of chart types to visualize the information easily.
What is Power BI?
Power BI is the beautiful, sexy, outer world, where the clients see amazing column charts, bar charts, pie charts, maps, slicers, matrix reports, KPIs in Dynamic, self-updating Dashboards.
PowerBI is simply a class apart!
Power BI Training in Singapore
In no other BI software have I seen so many ways to visualize, slice, dice, and navigate the data, so easily. I seldom have to teach the interface to any client – because it is so intuitive, easy to use, completely user-friendly, yet extremely powerful.
Power BI lets you create multiple ways to visualize the same information. It’s a breeze to create any visual, just by dragging and dropping the different measures, and slicing them by any dimension – by country, by geography, by business unit, by category, sub-category, by zone, by year, by quarter, by sector, by Product… almost anything you have in your data.
It can come from any dimension in any table within the data model.
Learn To Build Your Own Power BI Dashboards (Sample 2)
The best part of Power BI Visualizations is that it automatically filters other visuals immediately as you touch any bar or value in any other visual.
This allows us to see things in its entirety, without having to write any extra code or effort. Plus you can a good view of the pie of the pie or how much impact does one item have on the overall value?
We can see the data, sort it, export it, and see only one visual in the Focus mode.
Once you have analyzed the data in Power BI Dashboards to your heart’s content, it is time to spread the love, and share it with other colleagues and stakeholders who could benefit from the dashboard data analysis and visualization to make better, more informed decisions.
Learn To Build Your Own Power BI Dashboards (Sample 1)
You don’t have to send huge, heavy files by zipping to anyone. Simply use Power BI Online Services.
What is Power BI Online
Once the Power BI Visualization Dashboard is completed, it is time to Publish it Online, publically (Free), or to your Private Group of People within the Department or Division or Company (Paid) through the Power BI Online Services.
Power BI Online Services Training Singapore
You can share your dashboard with others by emailing them a link, and then they can consume it whenever, wherever, without installing any software. They can browse, slice and dice, visualize in any way, on any device, using any browser.
There is even a Phone View, which fits all the visuals perfectly on the phone, and makes it easy to check the KPIs on the fly.
The paid Power BI Services allow you to refresh the data every 3 hours, and you can even go to refresh it every half hour in the Enterprise server option (that’s akin to 48 refreshes each day)
See Power BI Reports in any browser – on any device: Microsoft Power BI Training at Intellisoft Systems, Singapore
For those with Write or Edit access, they can even make changes to the reports and dashboards and save another copy. You can publish as many dashboards, in different workspaces, and it works seamlessly with Sharepoint, Web, Azure and all other Online platforms, refreshing data on the fly and showing you the latest numbers.
A Perfect Package of Power, Simplicity & Elegance
All these features packaged together make the whole thing work seamlessly. You don’t even realize when you moved from Power Query to Power Pivot to Power BI to the Online Services. It just feels one simple to use package that does it all.
Microsoft has put in a lot of effort to design a state of the art, cutting edge Business Intelligence Software for the information-hungry business world.
Unlike Microsoft Office, which only gets updated every 2-3 years, Power BI suite gets updated each month, with multiple features, and even new DAX formulas being released each month.
Start the Exciting Journey of Gaining Business Insights With Microsoft Power BI
It’s time to embark on the journey to understand, use and implement Power BI in your business. Help the business make better decisions with updated information available to the decision-makers. Give them the ability to slice and dice data without waiting for IT or analysts to prepare the reports manually.
Be future-ready. Don’t wait till all your competitors are using it to gain an edge.
Be the force leading change in your business. Get Started Today!
Intellisoft Systems offers 2 day hands-on workshops for Power Query, Power Pivot, Power BI & Online Services, that have been extremely popular in Singapore for SME and MNC companies embarking on the Power BI suite of products.
Power BI Training in Singapore
Written By: Vinai Prakash
Vinai is the founder & Managing Director of Intellisoft Systems, a leading Training company based in Singapore. Vinai writes regularly for the Straits Times, leading magazines and newspapers, and conducts several workshops around the world sharing his knowledge in Business Intelligence, Data Warehousing, Data Mining & Data Analysis.
Contact us to attend a training or to organize a workshop for your entire department or company to benefit from Vinai’s impactful data analysis techniques and practical, hands-on approach that has won the accolades from thousands of delegates from around the world.
Article Written by Vinai Prakash, MBA, PMP, GAP, ACTA Certified
Just see the image below & check where you see yourself – in Reporting Hell or Reporting Nirvana…
Manual Reporting Hell
If you are trapped in Reporting Hell, here’s your Typical Reporting Day
Step 1: Bring the latest data files each month. This could be in Text, CSV, Excel files. Step 2: Open each file.
Step 3: Format Dates, Numbers & Text correctly in each data file. Step 4: Remove Duplicates and check the data rows don’t have any junk data.
Step 5: Build Calculation Formulas to work out interim values. Step 6: Once the data is in good shape, Copy and paste this into the Master Data File in Excel File.
Step 7: Change Data Source of Dropdowns, Pivots, Charts to include new data pasted in the master file. Step 8: Refresh the Report manually for each pivot, chart or data source.
Step 9: Repeat the same steps for each data file. This is a laborious task of data cleaning, transforming & loading the data, called as the ETL process. Step 10: Test to see if all well well, and that you did not miss anything.
Step 11: Publish the Report online & Inform the key stakeholders of the new report availability. Step 12: Keep Fingers Crossed!
Typical Total Time Taken: 6 Hours to 3 Days at a minimum
It is extremely frustrating & time-consuming to do the same manual steps each month to load the data, clean it, transform it and update all reports manually.
Most data analysts spend almost 90% of their time in loading & cleaning the data. They hardly have any time left to analyze or visualize the data to help the management make better decisions. They are stuck in doing the mundane, routine things, which is a total waste of their time.
If you are in such a situation, you are living in Reporting Hell.
It is time to move to completely automated, dynamic reporting, that self-updates each day, week or month, and your users can consume the reports on any device, instantly. It is like living in Reporting Nirvana – a completely amazing and refreshing world, where you spend most time analyzing data productively.
Care to know more about Reporting Nirvana?
Reporting Nirvana: Typical Day
In Reporting Nirvana, here’s what your typical day looks like:
Reporting Nirvana With Power BI
Step 1: Bring the latest data files in whatever format. Step 2: Open the KPI Dashboard Report, which automatically picks the latest data files
Step 3: Everything is automatically Refreshed Step 4: Enjoy your coffee!
Typical Total Time Taken: 3 minutes to 15 minutes at a maximum.
Achieve Reporting Nirvana
It’s time to move to Reporting Nirvana With Microsoft Power BI automation tools – Power Query, Power Pivot, & Power BI or with Excel Dashboard Creation Techniques. Imagine the amount of time you can save by doing the manual steps only once, and getting them executed each week & each month automatically, without having to do anything?
Learn how you can achieve Reporting Nirvana, and spend your day doing smarter things, analyzing data and contributing to growing your company’s topline and bottom-line revenue! Contact us at training@intellisoft.com.sg
Cheers, Vinai Prakash Founder & Master Trainer: Intellisoft Systems
With Virtual offices, global clients and multiple time zones, most business communication is happening over email. Yet, most people struggle at writing great, effective and professional emails.
The result: People judge you by your emails. If the email seems unprofessional, or buggy with mistakes, your and the company’s reputation is immediately tarnished.
Fortunately, it is extremely easy to write effective, and professional emails within a few minutes. Just remember to take note of the following tips.
1. Subject lines: Always use a simple, to the point subject line. Ideally, it should be no longer than 5-7 words. Most people look at the subject line to decide if they are going to open, and read the email, or simply delete it, irrespective of the content.
2. Greet: Always provide a simple greeting at the beginning of the email. It could simply be Hi Susan, or Hello Richard.
3. Thank for something: If this is a follow up email on something, or you are replying to someone’s email, it is a good idea to thank the person for replying to you. Thanks a lot for getting back to me so quickly. or Thanks for your time on the phone, or Thanks for the meeting. By thanking the person, you make it a more pleasant email, and the recipient is put at ease with your nice comments.
4. Reason / Objective of Email: The key reason for writing the email should be very clear and concise. No need to write long winded emails, as noone has time to read that much. People nowadays scan emails to quickly find the most important thing, and then decide what to do with it.You should quickly come to the point, like
We need to meet for a short while to discuss and resolve xyz, or I need your help to review xyz, or I am writing with regards to xyz.
5. What you Want them to do / Ask: People scan emails to find out if they have to do anything about it – keep it, file it for future, or take any action or not. If you want them to do something for you, you must state it clearly. If this is not clear, then they won’t do anything.Keep it short, simple and easily scan-able. For example,
* I want you to arrange a meeting between the 3 parties – possibly by 15th of the month. * I would appreciate if you could review the attached proposal, and get back to me by the 15th Nov.
*Could you please reply which time suits you better – Monday at 11am or Tuesday at 4pm?
By putting an action, and an action by date, you clarify what the recipient has to do, and by when. There is no room for ambiguity, and you are more likely to get what you wanted, by the due date.
6. Add Closing Remarks: It is a good idea for you to thank the recipient one more time, and add some nice, polite closing remarks, like: Thanks for your help and support.
Thanks for your cooperation and support. Feel free to contact me if you have any questions or concerns. I look forward to hearing from you.
7. Signature: It is a must to have a simple yet clear signature. You should not end with Cheers or See Ya unless it is a friend you are writing to. For business writing, you must always say, With Best Regards, or Sincerely, or Thank You. Regards, Richard.
8. Spell Check & Grammar Check: It is essential to always do a spelling check and a grammar check. Nothing spoils a good email than a few typos. All good email packages have a built in spell check. You should make use of it, and even set your email configuration to always do a spell check before sending.This can make your emails more professional and set up you in the eyes of the recipient.
Common Pitfalls in Writing Professional Emails & How To Avoid Them:
* Writing in All Capital Letters: Writing in all caps is considered offensive, rude on the internet. You should write Subjects in Title Case to stand out. The email body should be using normal English language rules – first letter is in capital, and the rest in small case.Use appropriate punctuation, and avoid using too many exclamation marks or question marks etc. It looks quite amateurish.
* Using Vague Subject lines: Do not try to fool the recipient to lcick your email by sending surruptious email subject lines or shady ones.Click for BONUS, or Find the Good Stuff <<<-CLICK HERE, or SEE WHAT JOHNNY DOES TO GET THE PASSES are quite spammy, and should be avoided.
* Attaching a huge image or attachment: Don’t attach huge files as attachments unless really required. You can always upload the file to some server or dropbox etc and provide them with the link to download if they wish to.This way you won’t become their enemy for jamming their mailbox with huge emails.
* Not Using Professional language: While you could use the English language to the fullest, and use big words, it is not always recommended. You should write emails as if a high school kid is going to read it. Most people’s vocabulary is not huge, and they don’t read much books or learn new things beyond school years.Some experts go so far to say that you should stick to a Primary 6th grade English to be perfectly clear to everyone.
* Not Using Formatting, Paragraphs, Headings: Do space out your emails with paragraphs, and points, or headings. Don’t write in huge block paragraphs of 8 to 10 lines or more in each block. It becomes pretty difficult to read, and you can lose a lot of people from getting your message.Use bold or underlined text in some important areas to make them stand out, but don’t bold every thing.
* Lengthy emails That Are Not To the Point: Write only as much as you need. Don’t start a long winded email that goes on and on.We are writing an email, not a sales page.
* Use of Emoticons: For processional email,s, it is not recommended to use any emoticons. So don’t try to act cute on business emails.Stay to the point, clear, and professional as possible.
Using templates in Access 2013 is easy because, as with other Office programs, you can select a template right on the new document screen. Office 2013 in general boasts a lot of helpful templates to get you started on projects more quickly, and you’ll notice some interesting ones in Access 2013. There is a selection of table templates. You can take a pre-designed table with the required details. You can also start with design that somebody else has made for a particular type of data.
You can put documents together pretty quickly in the other programs and can customize on your own. An Access database can have so many elements that using something like an inventory database with reports already set up really made sense. These templates are usually adaptable enough that they make document creation easier in other applications, too. You can also try this by joining us.
Apps in Access 2013
Application in Microsoft Access 2013
Access 2013 users will notice a new emphasis on apps–as in building your own, in addition to using pre-made apps. You might group a couple of templates into an app or create an app dealing with customer data, for example. All this is made easy thanks to a user-friendly interface for general users.
So as far as apps go, you have the option to find one in the Office store, find a Web App Template, or customize your own Web Apps.
A Focus on the Web: Office 365, SharePoint 2013, and Access 2013
Focus on Web
Companies have been able to host their own database through SharePoint and SQL Server on their internal network.
A company that also subscribes to certain Office 365 plans with SharePoint can now host Access 2013 databases in the Cloud, set with public or private permissions.
A focus on the web is seen throughout all Office 2013 applications, with easy sharing to SkyDrive and social media.
SQL Server: Behind the Scenes of Access 2013
SQL Server
SQL Server has been improved in Access 2013, which translates to more opportunities as well as better speed and reliability.
Specifically, SQL Azure Databases will be available for some Office 365 business plans.
Because of all the SQL improvements, now you also have more options to manage connections in Access 2013. Permissions are more advanced in Access 2013. You will also be able to more readily incorporate advanced reporting tools such as Power View, Excel, and Crystal Reports into your Access 2013 database. These allow for greater analysis of data.
If you have any further questions or want to join Microsoft Access 2013 Training class then contactIntellisoft!!!
Do you know that Microsoft Excel comes with a plethora of new features. It is worth investing the time to learn exciting Excel features, and make the most of the latest and greatest spreadsheet software by Microsoft.
So what is so great about Microsoft Excel? Plenty. Our Master Trainer, Vinai Prakash take you to a tour of the new features in Excel.
Do You Know These Advanced Excel Tricks?
1. Quick Analysis Tool: The new Quick Analysis tool allows you to convert your data into a chart or a table in just two steps. Preview your data with conditional formatting, spark lines, or charts, and make your choice stick in just one click.
2. Instant Flash Fill:Flash Fill enters the rest of your data in one fell swoop, following the pattern it recognizes in your data.
3. Chart Recommendations: With Chart recommendations, Excel recommends the most suitable charts for your data. Get a quick peek to see how your data looks in the different charts, and then simply pick the one that shows the insights you want to present.
4. New Functions: There’s a whole slew of new functions in the math and trigonometry, statistical, engineering, date and time, lookup and reference, logical, and text function categories.
5. Smart Pivot Tables: In Excel, When you create a PivotTable, Excel recommends several ways to summarize your data, and shows you a quick preview of the field layouts so you can pick the one that gives you the insights you’re looking for.
In the new Excel Data Model, you’ll be able to navigate to different levels more easily. Use Drill Down into a Pivot Table or Pivot Chart hierarchy to see granular levels of detail, and Drill Up to go to a higher level for “big picture” insights.
6. Improved Collaboration Tools: Working with other people on shared files in real time is a double-edged sword. While it’s useful to do this, you will face problems when two people try to change the same item at the same time. In Excel you can share and work collaboratively on files with others via SkyDrive using the Excel WebApp, and multiple people can work on the same file at the same time.
7. Standalone Pivot Charts: A PivotChart no longer has to be associated with a PivotTable. A standalone or de-coupled PivotChart lets you experience new ways to navigate to data details by using the new Drill Down, and Drill Up features. It’s also much easier to copy or move a de-coupled PivotChart.
Learn Excel in 2-3 Short Day Training: With so many new features, it is important that you learn these features, to improve your efficiency, productivity, and make use of these new features. After all, what is the point of using the latest software if you do not use its latest and greatest features.
Learn to use Excel features like Vlookup, Macros, Pivot Tables, Pivot Charts, Tables, Advanced Functions and Formulas, Sharing, Collaboration, Removing Duplicates, and much more…
Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Python, Power BI, Project Management, IT, Soft Skills & more with our Email Newsletter. Plus get the latest news on Grants. Join Today!
