5 Lessons Learned for Using Power BI/Power Query

All 0 Comments

According to David Francis, founder of Innovative Construction Technology and popular construction technologist YouTube video presenter, “One of the most underutilized tools in construction is information.” When it’s used in a Business Intelligence (BI) format to create dynamic reports, it can be especially powerful and preferable to the static information in a spreadsheet.

David explains that the strength behind Microsoft Power BI is Power Queries. They help you massage your data into a template form that works with your Power BI reports. As compared to working with static spreadsheets, using Power Query tools can enable you to generate intelligent, interactive Power BI reports that will benefit from instant updates.

Power BI Desktop is available for download at no charge. David calls it his favorite version: free. With Power Query in Power BI, you use query tools to visually tell your project’s story. The features include:

  • Interface – Reminiscent of other Microsoft menus, there are options for centralizing and streamlining business intelligence data sources and reports.
  • Data setup – You can pull data from Excel and many other sources. Create a data set by loading your data or make changes to it before loading. You can then shape the data (adjust columns, rows, tables) for presentation. 
  • Power Query setup – This involves the ribbon to interact with the data, queries pane for selecting or changing queries, data view, and query settings showing steps.
  • Visuals – Your reports can include Power BI visualizations such as charts (area, bar, column, line, combination, pie, doughnut, ribbon, waterfall, scatter, bubble, funnel, gage, influencers, KPIs), cards, maps, tables, and others.

David Francis is a pipefitter with 35 years of experience in construction. He has been taking advantage of 3D modeling since 1989 – as he puts it, “. . . before it was BIM.” For sage advice from David on Putting the “I” in BIM, check out episode 27 of the Bridging the Gap podcast.


David refers to “data wrangling” when you’re taking data from your building information model (BIM) or other locations, hopefully in good condition (although not perfect), and transform or clean it up for use in reports. This data normalization ensures your data adheres to standards and is in a format that makes sense to everyone receiving the report.

In generating reports, you can use filters for your visuals and use the pre-defined stock visualizations built into Power BI. David’s advice is, “Keep it simple. I like the ‘less is more’ attitude.” He reminds that someone has to read the report, so simple is best, including limiting the use of colors. 

Over the year that David has been using Power BI, he has learned some lessons:

  1. Make sure your data is good before you start.
  2. Make sure your units are a type number with no text characters in the field, otherwise they will not sum.
  3. Be careful when applying filters that are temporary.
  4. Be careful with data slicers because they might apply a filter you don’t realize is there.
  5. The Internet is a resource for tutorials and for answers to questions, for instance:
  6. Community – www.community.powerbi.com
  7. YouTube videos – Enterprise DNA, Radacad, GuyInaCube

If you’d like to see David’s demonstration of Power Queries in Power BI, you can watch his MEP Force 2020 class, “Death to your spreadsheets!! Power BI/Power Query Basics.” The demo begins about 13 minutes into his video session, which took place on Wednesday, September 2 at 2:00 pm.

Applied Software can help you understand your data, build data models or do complete Power BI dashboards. Contact Applied today to get started on your data visualization journey.


Invest in your employees’ success by choosing training with Applied Software LiveLab Learning. Effective now through October 31, 2020, buy one LiveLab Learning course and get 50% off the purchase of another course – no limit. Help employees develop skills while building trust and loyalty in your company.


Leave a Comment