Bottom line? Microsoft designed Power BI for business users to do self-service analysis, in some cases with the help of IT to set up gateways or internal "Content Packs" connecting to internal data. It's not as robust a platform as, say, Tableau -- but it also doesn't carry Tableau's robust price tag for private data analysis. (Tableau Public is free but has less functionality than the paid software and isn't designed for sharing private data, which is usually critical for enterprise users.)
If you are a full-time data analyst or commercial designer creating publication-quality graphics, this may not be for you. But if you don't need high-end customization and functionality while analyzing data and sharing your work, this could be an attractive alternative.
Data cleaning via scripts
If you program, you might be more comfortable preparing your data for analysis with a scripting language like Python or R; I know that I am. Obviously, you can prep some data with any scripting language, save a new file and then pull that resulting file into Power BI. But with R, you can run R scripts right from within Power BI, including refreshing data from those scripts right inside Power BI (the Desktop version).
To run a script, go to Get Data > Other > R Script and you'll get a dialog box to execute an R script. You don't actually have to copy and paste your entire script into the dialog box; just use the R source ("path/to/filename.R") function, making sure you use the full path to the script. Any data frames that are creating by your script will then appear for possible import into Power BI.
Power BI also supports a couple of Microsoft scripting languages: M (the Power Query Formula Language) and DAX (Data Analysis Expressions).
M is the language that underlies querying and mashing up data. What you do in the Query Editor has M underneath. If you see automatically generated scripts in the formula bar within the Query Editor, that's M.
Once you've loaded your data from the Query Editor into the Power BI report view for analysis and visualization, the scripting language is DAX. It's designed so Excel power users will be comfortable, and has similar syntax and functions to writing Excel formulas. If you create a new column in your report view, the formula bar there is DAX. If you want to do a lot of complex data work within Power BI, at some point you may be interested in learning DAX.
Like Excel formulas, though, DAX is not as robust as a scripting language like R. For example, its SUBSTITUTE function will just replace one exact text string with another and does not use regular expressions that can match a pattern such as "find the letter A followed by two or more numbers, with or without a space in between."
Sign up for CIO Asia eNewsletters.