Published Date : August 15, 2015
When you get data in Power BI you actually use Power Query Component. In this chapter you will learn about What Power Query is, and what are different types of sources that Power Query can connect. Power Query has also great list of transformations that can be applied on the data set as well (which will be covered in next chapter), and the Power Query formula language M can be used for complex and powerful data transformation situations (will be covered in a chapter after).
In this section you will read an introduction to Power Query. You will learn;
Power Query previously named as Data Explorer. Data Explorer has been released as public preview for the first time at February 2013. Data Explorer then renamed to Power Query at July 2013, and from that time it had lots of enhancement on the product. Power Query is on a regular and frequent update plan by Microsoft team and usually you can see monthly updates on this, here is the latest update notes (released yesterday!) Power Query has been tested a lot during this period and nowadays used in many real world data transformations and BI solutions.
Power Query is a data extraction and transformation engine. The engine comes with a formula language and a graphical tool. The graphical tool has two major setup versions; one embedded in Power BI Desktop tool, and the other one as an Add-In for Excel. The graphical tool has list of transformations that can be applied on a data set, and it also supports different data sources. However the Power Query formula language is much more powerful than the GUI. Actually there are some features in Power Query engine that not yet has been implemented through GUI, but they are available through M (formula language).
Power Query can connects to set of data sources and read data from them. Set of data sources is variable from text files, to web urls, from database systems to some applications. Wide range of data sources is supported. So to respond to one of the very first questions that usually appears when I introduce this product that Can Power Query connect to Oracle? Sure it does! Not only Oracle, but also MySQL, PostgreSQL, DB2, Sybase, and Teradata.
Power Query can apply many transformations to the data set. you can apply simple transformations such as trimming a text value, and applying numeric calculations to complex transformations easily such as pivot and unpivot. Power Query uses a function library for applying transformations, and the function library contains heaps of transformations for every data type such as table, text, record, list, date, number and so on.
Power Query graphical interface is so easy to work with that even business analyst or a power user can work with it, on the other hand Power Query M language is so powerful that can be used for complex real world challenges of data transformations. Power Query can load the result set into an Excel spreadsheet or it can load it into Power Pivot for data modelling. The version of Power Query used in Power BI Desktop load the result set into a Power Pivot model. I will go through details of Power Pivot in future chapters, for now it would be enough to know that Power Pivot is In-Memory tabular data model engine. Here is an screenshot or the Query Editor window
In below you can see a high level diagram of Power Query conceptually:
Power Query is available in three different setups:
So if you want to install then you have to install one of the options below:
Excel Add-In for Excel 2010 and 2013:
https://www.microsoft.com/en-us/download/details.aspx?id=39379
Please note that the link above might change because Power Query updates frequently and new version will be available almost every month. so you can simply Google it as Power Query Excel add-in.
Excel 2016 download link:
https://products.office.com/en-us/office-2016-preview
At the time of writing this blog post Excel 2016 is in preview stage, so the link is likely to change.
Power BI Desktop:
https://powerbi.microsoft.com/desktop
With Power Query you can connect to wide range of data sources. SQL Server or DB2 or Oracle…. All of these database are supported as a source. You can even connect to an Analysis Services instance and fetch data from it. You can connect to file data structures such as text files, XML, CSV, and Excel. You can even read list of files in a folder! You can connect to a range of applications such as Facebook, Salesforce, CRM Online and etc and get data from them. You can get data from Azure services such as Azure SQL database, Azure HD Insight, Azure Blob storage and etc. There are many data sources supported for Power Query (and obviously for Power BI), also more data sources will be available in every update of Power Query or Power BI.
Here is an example set of data sources supported in Power Query (Excel version):
File Data Sources
Databases
Azure
Other Sources
The Power Query version in Power BI Desktop supports some new applications that still is not implemented as Power Query for Excel, you see some of them below:
Power Query look at the data values with data types such as : Table, Record, List, DateTime, Text, Number, Boolean and etc. There are many data transformation functions for any of these data types. You can apply Merge (similar to join) or Append (similar to UNION) to two tables. You can apply text functions such as getting part of a string, trimming it or length of string. You can apply mathematical functions. You can apply datetime functions such as functions for year, Month, day and week. There are two way to apply these transformations;
Query Editor will give you a great experience of most common transformations through very easy user interface. you can apply most of transformation with matter of few clicks. The Query Editor in Power BI Desktop or Power Query Add-In for Excel has many common transformations listed. You can see some of them in below screenshots:
M is the formula language behind the scenes of Power Query. Everything you do in the Query Editor will be translated to an M script. M contains full list of functions that you can use. So the powerful side of Power Query is actually M. I will go deep into details of M in this book, because you would need it for solving complex challenges. M is a functional language and it has a simple structure. screenshot below shows an M Code. The details of information about M scripting will be covered in next sections.
You can use Power Pivot as the destination for Power Query to load result set into a data model, or you can use simple Excel spreadsheet for loading data. If you use Power BI Desktop the result set of Power Query automatically will be loaded into a model.
This question might sounds weird at first glance, but makes sense when you think about it that all features I mentioned above are available for free! You don’t have to pay anything for it. Getting data from different sources, applying all kind of transformations to it, and loading it into a data model is all free. So now the question makes sense; What are features of Power Query Premium?
Using Data Catalog
Data Catalog is a metadata definition service that you can define data sources from your organizational data stores or from public data stores that you trust. You can define descriptors for the data structure so Power Query can search through the Data Catalog and fetch information based on it.
Sharing Queries
You can share your Power Query scripts and queries within your organization
Management using of Shared Queries
You can check the usage of queries that you’ve shared
As you see in above most of features for Power Query Premium is related to Office 365 usage for sharing or Power BI and Azure for data catalog and structure. Most of the features in Power Query (Essential features I have to say) is available for free!
In summary in this section you’ve learned about What is Power Query and what are components of it, you’ve learned features of Power Query, and now you are probably thinking about usage of it in scenarios and challenges that you might have right now! Good start, in next sections I will go through experience of getting data with Power Query and Power BI Desktop.
Published Date : August 11, 2015
In previous section (from the Power BI from Rookie to Rockstar book) you’ve learned about Power BI Desktop, and you’ve developed a sample report with that and published it to Power BI website. In this section I’ll explain some of Power BI website’s functionality. You will understand at the end of this section that Power BI is not just a host for Power BI reports, it gives you option to connect to data sources, and build reports as well, All in web browser! You won’t need to install anything to use it. I’ll also show you Power BI app for mobile and we’ll explore it to see what it gives us in overall.
In this section I will go through below modules with you;
I have to mention it again that this section is still part of Introduction to Power BI chapter of the book, and topics above will be covered lightly. Later in future chapters we will go through all options above deeper in details. Purpose of this section (and the whole Introduction to Power BI chapter) is to gives you an understanding of Power BI tools and services, and prepare you for detailed discussions in future chapters.
Power BI Website is a location to host your *.pbix (Power BI) files. You can publish Power BI files directly from the website, or from Power BI Desktop (As you’ve seen in previous section). However Power BI website is not just for hosting these reports. You can create dashboards from reports in Power BI and share dashboards with others. You can also connect to some data sources directly from the website, and create the report online. Yes, you can do it all with just your web browser. You can also edit an existing report, and re-write it, or save it as a new report. You can schedule data sets to refresh automatically (not all data sources supports this feature). As you see Power BI website is not just a hosting location then, it is much more. Sometimes you can do some stuff just with the web browser.
You can edit reports in the website and rewrite them, or you can save them as a new report. You can also create a new report from an existing or new data source. Let’s use the data set that we’ve built in previous section and use that as the source for our new report. Follow the instruction below to create a report in the website.
Login to PowerBI.com website with your login. From the left pane menu under Datasets you’ll see Pubs Analysis (the data set that we’ve built from previous example). You can also see Pubs Analysis under reports as well. Pubs Analysis report is the visualization report we’ve built, and the dataset just contains metadata, and the data. click on ellipsis button near Pubs Analysis data set and choose explore from the popup menu.
When you click explore, a report window will open in the main window. This is similar to Report tab in Power BI Desktop. You’ll have the Visualization, Fields, and Filters pane in the right side (numbered as 1 below), You can add pages (numbered as 2), and you can save the report, or view that, or add a text box to it (numbered as 3). Here is an screenshot of the report editor online
The report editor is not exactly similar to the report tab of Power BI Desktop, there are less functions here (For example you cannot add image here). But most of the functionality is available through the online report editor. and it works perfectly with the web browser. Let’s build a simple report;
Create a “Line and Stacked Column Chart” from the Visualization pane. Ste Shared Axis as Order Year (from sales table), and Column Series as city (from stores table), and Column Values as sales amount (from sales table), and finally Line Values as qty (from sales table). The report illustrated below;
Let’s create another visualization in the report. Add a Funnel to the report. Set Group to city, Values to qty. Also set Color Saturation to sales amount. Color saturation will highlights the funnel bars based on sum of sales amount.
For the last chart in this report, let’s build a pie chart. I know that pie chart is not good chart for visualization, But I want to use it for 3 distinct categories with really distinguishing values. (Please remember that don’t use pie chart with categories are more than 3 and 4, and values are quite close to each other). Add a pie chart to the report. Set it with Legend as state (from stores), and Values as qty. below is the view of pie chart
Now let’s save the report with name of Pubs Analysis Extended (save option is in top left side of the report editor). You now see the new report under Reports. if you click on it you’ll see it in the viewer.
You can build dashboards from your existing reports. dashboards will be primary view point of you or users (from website or mobile app). To create a dashboard, click on Add button close to Dashboards in the left pane, and create a new dashboard with name of Pubs Dashboard.
An empty dashboard will be created. Now go to Pubs Analysis Extended report and click on the right top side of funnel chart to “Pin Visual” as below. You’ll see a success message that chart pinned to dashboard.
Go to Pubs Analysis report, and pin visual the Map visualization as well. now the Pubs Dashboard looks like below screenshot;
If you click on any item in the dashboard you’ll be redirected to the underlying report.
Dashboards can be easily shared with other users. just click on ellipsis button of dashboard and choose Share.
You can connect to some data sources from the Power BI website. This option is available through Get Data button.
There are some data sources supported in online Get Data. You can get data from services (such as Google Analytics, MailChimp, CRM, Salesforce…), or get data from Files (such as Local File, OneDrive), or get from Databases (such as Azure SQL DB, Azure SQL DW…). Some of these options requires Power BI Pro, but most of them are available through normal Power BI account. screenshot below shows services that supported to get data from (the list updates frequently, and every month new data sources will be added)
Power Q&A is the question and answering engine of Power BI which works based on natural speaking language. You can ask questions in English language from the Power BI dashboard and get responses! Power Q&A engine will translate your query to machine query and respond that with visualization of the data in the model.
To ask a question simply use the question bar in the dashboard
For example you can ask from Pubs Dashboard: “Show city sales amount”, and the response is:
you can even change the visualization to map with this question: “Show city sales amount on map”
Power Q&A is a very powerful engine. There are some tips and tricks of how to develop your data model so Power Q&A could generate best responses to questions. I’ll cover these tips and tricks in future chapters of this book.
Power BI app is available for Android, Apple, and Windows Phone. simply download it from Goolge Play (Android), or AppStore (Apple), or Windows Store (Windows Phone). After the installation login with your username, and you’ll see dashboards there. screenshot below shows the dashboard in Android phone, but other apps are similar to this.
You can highlights some parts of the report and share it as an image
You can even share it from Mobile App
In summary you’ve learned about options that are available to you from Power BI website. You’ve learned that even without Power BI Desktop you can create and edit reports and get data from data sources. You’ve learned how easy is creating dashboards and sharing it with other users. You’ve seen some examples of Power Q&A which answers your natural language questions with visualization items and the data in the model. You’ve also learned that Power BI Mobile Apps interacts nicely with Power BI site and dashboards.
In the next chapter we will go deeper through Get Data Experience of Power BI Desktop and will explore options for different data sources. Data transformations with M formula language also will be discussed in the next chapter.
Published Date : August 11, 2015
There are some reasons that I chose Power BI Desktop for the very first demo of this book. Power BI Desktop has three components all in one (Power Query, Power Pivot, and Power View), it can upload directly to PowerBI website, and it is a recently released product with many shining features. In this section I want to get you to start working with Power BI Desktop, you will get familiar with Power BI Desktop editor, and you’ll see main panes of that. This would be the first experience with Power BI Desktop. If you want to know What is Power BI read the first section of book here.
Power BI Desktop is the new development editor released 24th of July 2015 as general availability for Power BI. Power BI Desktop can connect to many data sources, transform the data, load it into a data model with relationship design, and finally visualize it in a report style. Power BI Desktop apply all of these actions on a *.pbix file, and it can publish the file directly to Power BI website where users can see the report and interact with it from web browser.
Power BI Desktop as mentioned above is an editor for three components; Power Query, Power Pivot, and Power View. Power Query connects to data sources and mash up the data with a formula language, the result set of Power Query will be loaded into a tabular model which is Power Pivot. Power Pivot can set the relationship and allow you to create measures and calculated columns and set the data model as you want. Then Power View connects to the model and visualize the data with different charts and visualization elements.
Power BI Desktop has everything in one editor, and this makes it an easy to use tool, but don’t be fool of this easy interface. Power BI Desktop uses three powerful components and two of those components give you extensive features (I mean Power Query and Power Pivot). You can solve very complex challenges with Power BI Desktop only because of it’s underlying components. To be a professional and expert in Power BI, you have to be an expert in Power Query (M language), and Power Pivot (DAX expressions). I’ll cover deep detailed discussion about these products in future chapters, but for now, let’s look at Power BI Desktop with a very easy example.
Power BI Desktop:
Download Power BI Desktop from this link. You can also install 64 or 32 bit options if you go to advanced download options.
Sample Database:
For this example I used the old Pubs database (just to create something different than AdventureWorks samples). you can download it here and install Pubs database.
Power BI Account:
If you don’t have a Power BI account, simply go to Power BI website, and enter your company mail address to get a free account. Note that you cannot use public email accounts such as gmail and yahoo here
Power BI Desktop like many other Microsoft editor applications has a startup screen. in the startup screen you have some options to go through as below:
Let’s start the work with getting data. Pubs is a database of some books published and sold in stores, information about titles, authors, stores and etc are available in this database. I want to keep this example easy so I create a very simple model. for every experience with Power BI Desktop you have to first get data from somewhere. wide range of data sources is supported from files to database, web search, and many other data sources. once you’ve connected to the data you can transform it (re-shape it) as you want. So in this part of the example you get data from Pubs database, and you’ll apply very simple transformation to it.
Open Power BI Desktop. In the startup screen choose Get Data.
Get Data window will open. This window is the graphical interface to connect to many data sources. from the left options choose Database, and in the main section select SQL Server Database. (Remember we are connecting to SQL Server instance that has Pubs database installed)
Next dialog box asks for server name and database name. Server name is necessary, but you can leave database name blank (blank will load all databases to choose from). You can also write a sql statement here if you want to. because my server for database is my local computer, so I just enter my computer name as below
You can set up credential in the next window. I use my current credentials, but note that there are other options such as entering an alternate credential or using database (SQL Server authorization)
you might get a message about encryption support, just click OK on that, we will cover that later. Because we’ve entered blank in the database name, now we see all databases, expand Pubs, and then select these tables: titles, stores, and sales.
As you see in above screenshot when you click on tables you’ll see a sample data rows of that appearing in the main section. this helps you to have an understanding of what you are looking at. Click on Edit. The Query Editor window will open. This is the main Power Query Window that has all the tables we’ve selected.
In Query Editor window in the left pane you can see three tables that we’ve loaded (numbered as 1). Main pane (numbered as 2) shows selected the data set from table in the left hand side. Right hand side shows the steps applied on the data set. the menu on top contains some transformations that you can apply on the data set.
Select sales table in left pane, now in the main pane you’ll see data columns of that table plus two hyperlinked columns (stores, and titles). Power Query is intelligent enough to understand relationship between tables and load them together. That is why stores and titles are here as hyperlinks, because Power Query joined them based on the FK-PK relationship in the database. Let’s don’t use that intelligence for now, so I’ll remove these two columns by right click on them and then selecting Remove.
Do the same in “titles” table, and remove columns: publishers, roysched, sales, titleauthor. Also from “stores” table remove discounts and sales columns. Now let’s make a very small change. I want to keep this example very simple, so regardless of best practices of having date dimension that have columns for year, months, week and etc, I just want to add a year column to the sales table. the year column that I want to add is the Order Year, and I can simply calculate that from the ord_date column which is a datetime value.
select sales query, then click on ord_date column, and then from the main menu click on Add Column. in the Add Column section under Date, choose Year.
A new column with name of Year will be added to the table, right click on the column and rename it to Order Year. Well done, you’ve done your first transformation with Power Query. It was so simple, wasn’t it?
Now let’s load data into a model, in Query Editor window under Home tab, click on Close & Load button. This will load all the queries with their data into the model.
You can see in the screenshot below that data is loading into the model
Now you will see the blank Power BI Desktop editor. From the left tabs, click on Relationship (last tab) as below to see the relationship diagram of your model
Now you see the diagram. Power Pivot intelligently created relationship between tables based on their relationship in the pubs database. if you double click on any of relationships you can modify it if you want to. Let’s just leave it as is, because relationship is just fine. The change that we want to do in the modelling is just adding a sales amount to sales table. (Note that sales table has the quantity only, and price is in titles table). Sales amount would be quantity sold multiplied by price of the title. Let’s do it in the model.
Click on Data Tab to see structure of data in each table. you can see that qty exists in sales table. If you look at titles, you’ll see price column there.
Go to sales table (from right pane choose sales), and then click on New Column. In the formula bar type in the formula below
Column = sales[qty]*RELATED(titles[price])
then press enter (you’ll see that intellisense also helps you to write down the formula). Now you’ll see the result in the new column. right click on it, and rename it to sales amount. Congratulations! you’ve written your first DAX expression here. Related function is a function that brings the relative row to this record based on the relationship of tables. so related(titles[price]) means from table “titles” bring column “price” but only price of that title that is related to this record (based on the relationship of title_id column.
Our model is ready now to be visualized in a report. Go to Report Tab (the default tab in Power BI Desktop). and click on stacked column chart from Visualizations pane. You’ll see an empty chart in the report window.
From the fields pane drag and drop sales amount into the Value section of the chart. then drag and drop city (from stores table) into Legend, and then drag and drop title (from titles table) into Axis. Done! you have a clustered column chart already. you can change height and width of the chart simply (I bet you’ll figure it out how to).
Let’s create a Map now. Click on an empty section of the report, and then click on Map. set Location as stor_address (from stores table), Values as qty (from sales table), and Legend as city (from stores table). Your map is now ready.
Let’s add a treemap to the experience as well. Set the treemap with Group as type (from titles table), Details as title (from titles table), and Values as qty (from sales table).
Let’s add a total quantity sold as a text box in the report as well. Add a Card from visualization, and choose qty to be displayed there.
Now add an slicer as the last item for your report. Slicer now can slice and dice the data by Order Year (remember we’ve added this column in modelling part)
Save the Power BI Desktop file as Pubs Analysis. In the Report tab of Power BI Desktop click on Publish (you’ll need to enter your account details here for the first time). You’ll get a success message when publish is completed.
Now open the Pubs Analysis in Power BI (just click on the link in publish window), and you’ll see your report online in web browser.
Your report is also fully interactive as well, click on charts and columns and maps to see how highlighting features works interactively.
Well done! you’ve made your first report with Power BI Desktop, and I know that you’ll say it was easy, fun, and interesting. In this section you’ve learned what is Power BI Desktop. You’ve connected to a SQL Server as data source and transformed the extracted data. You’ve loaded data into a model and added a calculated column to it. You’ve created a report with charts and visualization elements, and finally you published your report in Power BI website. In next section we will go to Power BI website and Apps to see how they works.
Published Date : August 8, 2015
As promised I started writing blog post series for the Online Book: Power BI from Rookie to Rockstar, and this is the first post.
Power BI is not a new name in the BI market, components of Power BI has been in the market through different time periods. Some components such As Power BI Desktop is such new that released as general availability at 24th of July. On the other hand Power Pivot released at 2010 for the first time. Microsoft team worked through long period of time to build a big umbrella called Power BI, this big umbrella is not just a visualization tool such as Tableau, it is not just a self-service data analysis tool such as PivotTable and PivotChart in Excel, it is not just a cloud based tool for data analysis. Power BI is combination of all of those, and it is much more. With Power BI you can connect to many data sources (wide range of data sources supported, and more data sources add to the list every month). You can mash up the data as you want with a very powerful data mash up engine. You can model the data, build your star schema, or add measures and calculated columns with an In-Memory super fast engine. You can visualize data with great range of data visualization elements and customize it to tell the story behind the data. You can publish your dashboard and visualization tool in cloud and share it to those who you want. You can work with On-premises as well as Azure/cloud based data sources. and believe me there are much more things that you can do with Power BI which you can’t do with other products easily.
There are many definitions for this tool, here is my version of it simplified for everyone to understand;
Power BI is a cloud based data analysis, which can be used for reporting and data analysis from wide range of data source. Power BI is simple and user friendly enough that business analysts and power users can work with it and get benefits of it. On the other hand Power BI is powerful and mature enough that can be used in enterprise systems by BI developers for complex data mash-up and modelling scenarios.
Power BI made of 6 main components, these components released in the market separately, and they can be used even individually. Components of Power BI are:
There are many other parts for Power BI as well, such as;
Some of above components are strong and has been tested for very long time. Some of them however are new and under frequent regular updates. Power BI built easy graphical user interfaces to follow, so a business user simply could user Power Query or Power BI desktop to mash up the data without writing even a single line of code. It is on the other hand so powerful with power query formula language (M) and data analysis expression (DAX) that every developer can write complex codes for data mash up and calculated measures to respond challenging requirements. So if you’ve heard somewhere that Power BI is a basic self-service data analysis tool for business analysts and cannot be used for large enterprises systems, I have to say this is totally wrong! I’ve been using Power BI technology myself in many large enterprise scale systems and applications, and I’ve seen usage of that in many case studies all around the world.
Power BI components can be used individually or in a combination. Power Query has an add-in for Excel 2010 and Excel 2013, and it is embedded in Excel 2016. The add-in for Power Query is available for free! for everyone to download and use it alongside with existing an Excel (as long as it is Excel 2010 or higher versions). Power Pivot has been as an add-in for Excel 2010, from Excel 2013 Power Pivot is embedded in Excel, this add-in is again free to use! Power View is an add-in for Excel 2013, and it is free for use again. Power Map is an add-in for Excel 2013, it is embedded in Excel 2016 as 3D maps. Power Q&A doesn’t require any installation or add-in, it is just an engine for question and answering that works on top of models built in Power BI with other components.
Components above can be used in a combination. You can mash up the data with Power Query, and load the result set into Power Pivot model. You can use the model you’ve built in Power Pivot for data visualization in Power View or Power Map. There is fortunately a great development tool that combines three main components of Power BI. Power BI Desktop is the tool that gives you combined editor of Power Query, Power Pivot, and Power View. Power BI Desktop is available as stand-alone product that can be downloaded separately. With Power BI Desktop you will have all parts of the solution in one holistic view.
To give you an overall view of what you would expect to see in each component I’ve put few explanation for each component here. There will be detailed description for all components later on in future chapters.
Power Query is data transformation and mash up engine. Power Query can be downloaded as an add-in for Excel or be used as part of Power BI Desktop. With Power Query you can extract data from many different data sources. You can read data from databases such as SQL Server, Oracle, MySQL, DB2, and many other databases. You can fetch data from files such as CSV, Text, Excel. You can even loop through a folder. You can use Microsoft Exchange, Outlook, Azure…. as source. You can connect to Facebook as source and many other applications. You can use online search or use a web address as the source to fetch the data from that web page. Power Query gives you a graphical user interface to transform data as you need, adding columns, changing types, transformations for date and time, text, and many other operations are available. Power Query can load the result set into Excel or into Power Pivot model.
Power Query also uses a powerful formula language as code behind called M. M is much more powerful than the GUI built for it. There are many functionality in M that cannot be accessed through graphical user interface. I would write deeply about Power Query and M in future chapters so you can confidently write any code and apply complex transformations to the data easily. screenshot below is a view of Power Query editor and some of it’s transformations.
Power Pivot is data modelling engine which works on xVelocity In-Memory based tabular engine. The In-Memory engine gives Power Pivot super fast response time and the modelling engine would provide you a great place to build your star schema, calculated measures and columns, build relationships through entities and so on. Power Pivot uses Data Analysis eXpression language (DAX) for building measures and calculated columns. DAX is a powerful functional language, and there are heaps of functions for that in the library. We will go through details of Power Pivot modelling and DAX in future chapters. Screenshot below shows the relationship diagram of Power Pivot
The main data visualization component of Power BI is Power View. Power View is an interactive data visualization that can connect to data sources and fetch the metadata to be used for data analysis. Power View has many charts for visualization in its list. Power View gives you ability to filter data for each data visualization element or for the entire report. You can use slicers for better slicing and dicing the data. Power View reports are interactive, user can highlight part of the data and different elements in Power View talk with each other. There are many configurations in Power View visualization that I will explain fully in future chapters.
Power Map is for visualizing Geo-spatial information in 3D mode. When visualization renders in 3D mode it will gives you another dimension in the visualization. You can visualize a measure as height of a column in 3D, and another measure as heatmap view. You can highlight data based on the Geo-grahpical location such as country, city, state, and street address. Power Map works with Bing maps to get best visualization based on Geo-graphical either latitude and longitude or country, state, city, and street address information. Power Map is an add-in for Excel 2013, and embedded in Excel 2016.
Power BI Desktop is the newest component in Power BI suit. Power BI Desktop is a holistic development tool for Power Query, Power Pivot and Power View. With Power BI Desktop you will have everything under a same solution, and it is easier to develop BI and data analysis experience with that. Power BI Desktop updates frequently and regularly. This product has been in preview mode for a period of time with name of Power BI Designer. There are so much great things about Power BI Desktop that cannot fit in a small paragraph here, you’ll read about this tool in future chapters. because of great features of this product I’ll write the a section “Power BI Hello World” with a demo of this product. You can have a better view of newest features of Power BI Desktop here in this blog post. screenshot below shows a view of this tool;
Power BI solution can be published to PowerBI website. In Power BI website the data source can be scheduled to refresh (depends on the source and is it supporting for schedule data refresh or not). Dashboards can be created for the report, and it can be shared with others. Power BI website even gives you the ability to slice and dice the data online without requiring any other tools, just a simple web browser. You can built report and visualizations directly on Power BI site as well. screenshot below shows a view of Power BI site and dashboards built there;
Power Q&A is a natural language engine for questions and answers to your data model. Once you’ve built your data model and deployed that into Power BI website, then you or your users can ask questions and get answers easily. There are some tips and tricks about how to build your data model so it can answer questions in the best way which will be covered in future chapters. Power Q&A and works with Power View for the data visualizations. So users can simply ask questions such as: Number of Customers by Country, and Power Q&A will answer their question in a map view with numbers as bubbles, Fantastic, isn’t it?
There are mobile apps for three main mobile OS providers: Android, Apple, and Windows Phone. These apps gives you an interactive view of dashboards and reports in the Power BI site, you can share them even from mobile app. You can highlight part of the report, write a note on it and share it to others.
Power BI provide these premium services for free! You can create your account in PowerBI.com website just now for free. Many components of Power BI can be used individually for free as well. you can download and install Power BI Desktop, Power Query add-in, Power Pivot add-in, Power View add-in, and Power Map add-in all for free! There are some features of these products that reserved for paid version however, such as Power BI Pro which gives you some more features of the product. If you want to learn more about pricing of the Power BI I encourage you to read this page. However create your free account today and give it a try, it won’t cost you anything except your precious time, which I’d say definitely worth it.
To follow examples of this book download the latest version of Power BI Desktop from here. Most of examples will be demoed through this product, however there might be some exceptions, which I will mention at the beginning of the specific section if you need another tool to download. for the data source for some demos I’ll use AdventureWorks database examples, it might be the database itself or the tabular model or other versions and shapes of that, I’ll write more information about the requirement for running demo at the beginning of each demo. Files of demos will be shared, either *.pbix files (Power BI Desktop files) or Excel files for you as a reference. If you have any questions just use the comment section below each post.
If you want to have a clue about what to expect in this book read the table of content here.
In summary you’ve read about what Power BI is, and what are Power BI components. You’ve learned that Power BI is cloud based data analysis tool that can be used by data analysts, business analysts and power users because it is easy to use, however it is so much powerful that can be used to answer complex BI requirements. In next section I’ll explain how to use Power BI Desktop for a Hello world example. You’ll learn some great features of this product through a demo and you will have a better understanding of Power BI tools.
Published Date : August 7, 2015
The idea of writing a book on Power BI is not new for me, I’ve been thinking about it for a while, even before first release of Power BI Designer. However Power BI is fast paced technology, Microsoft Product team is doing great job with monthly , and sometimes even more frequent updates. With this fast pace release any book publishing would be out dated once the book comes out in the market. Publishing a book takes time, writing TOC, then chapters, building demos, peer reviews, proof reading, test, feedback, revise on reviews, and publishing take long time. I’ve been in publishing process of some books and I am completely aware of that. So I though it would be better to write a book online on this blog rather than spending heaps of time on paper publish. There are some reasons that I thought about writing this book online and writing that for free which I like to mention in this preface.
There is nothing wrong with publishers such as Apress, Wrox and etc, these are great publishers. However I have to spend lots of time to get them read my proposal first, and then set the schedule, and write, review…. to publish. This is a long way as I mentioned above. With fast-paced technologies such as Power BI this long process won’t work, actually the time that book be available in the market it will be out-dated for sure. When I write it online, I can write faster, I can write like a blog post, and I can write it everyday. If technology updates, I’ll write an update blog post. It is all easy and on-time.
I never write book for money, I write because I like to get a wider audience in front of me, and tell them about the great product, and best practices of doing things with that and so on. With famous publishers I would definitely get more audience. However when the content be available for free, and online then everyone would read it, search engines would direct audience to this content, and audience range will expand.
Haven’t you heard the news? Power BI Desktop released with superb features, there is a massive push in Microsoft for updating Power BI regularly and frequently which makes this product better and better each month, There are so much awesome things that you can do with Power BI, and many other reasons. I actually have to search for reasons why not Power BI!
Well, I planned for writing at least a post every day for that, and with the count of days till end of the year (about 147 days from now), even if I could make only 100 days of writing on this book, I’ll be able to have it in a very good stage at end of 2015. That said, the content would be available everyday, so you won’t need to wait till end of the year to read something. you can just read it everyday. I encourage you to subscribe to this blog to receive email when the content is available.
Feedback, Comments, Questions, Real-World Challenges about Power BI. I’m writing blog posts anyway, it will get better and better if I get feedback from you. I can put more examples if I know your real-world challenges with Power BI. I can answer your questions along the way, that answer might help some others with similar problems as well.
BI Developers and Consultants who want to know how to develop solutions with this technology. BI Architects and Decision Makers who want to make their decision about using or not using Power BI in their BI applications. Business Analysts who want to have better tool for playing with the data and learn tricks of producing insights easier. The book titled “Power BI From Rookie to Rockstar”, and that means it will cover wide range of readers. I’ll start by writing 100 level and we will go deep into 400 level at some stage. So if you don’t know what Power BI is, or If you are familiar with Power BI but want to learn some deep technical topics about Power Query M language, then this book is for you.
Like any other books I like to share the Table of Content here. The Table of Content is highly subject to change at this stage. I’ll update the table of content along the way.
Table of Content
TOC above is subject to change. Stay tuned for next blog posts.
Published Date : August 3, 2015
Fetching list of Files in a folder is easy with Power Query, it is one of the built-in source types. However fetching list of folders is not a built-in function. In this post I’ll show you how to fetch list of files, and also fetch only list of folders. The method explained in a way that you can customize the code and apply any conditions as you want later on. Conditions such as File or folder name masking to fetch only names that contains special character strings.
For fetching all files in a folder you can simply use the GUI Get Data, and under File, choose Folder
Browse for Folder
and then simply you will see list of all files.
As you see in the above table extracted, there is a column named Content, which has the content of the file. you can click on that to see content of the file if you want.
What is the M Code Written Behind the Scenes
The code behind the scenes for this transformation used Folder.Files function
let
Source = Folder.Files("C:\Users\Reza\Dropbox\Speaking")
in
Source
There is another M function to fetch all files and folders listed under a folder, named Folder.Contents. This function returns the Content column with data type of the record and content of it. for Files data type usually is Binary, and for Folders it is table. So it can be easily distinguished and separated. Here is list of all files and folders fetched:
let
Source = Folder.Contents("C:\Users\Reza\Dropbox\Speaking")
in
Source
Result set contains both files and folders
As you see the Content column shows the data type of the value. To check if a record is Folder or not, we have to compare its data type to Table. We can add a column to the table to check data type of the content column in each record. data type can be checked with Value.Is function.
let
Source = Folder.Contents("C:\Users\Reza\Dropbox\Speaking"),
TypeAdded=Table.AddColumn(Source,"Type",each Value.Is([Content],type table))
in
TypeAdded
And the result set shows if the record is folder or not
To fetch only folders, we can filter the data set with Table.SelectRows function. I’ve sorted the result set descending by creating date of the folder. Here is the code:
let
Source = Folder.Contents("C:\Users\Reza\Dropbox\Speaking"),
TypeAdded=Table.AddColumn(Source,"Type",each Value.Is([Content],type table)),
Folders=Table.SelectRows(TypeAdded, each [Type]=true),
Sorted=Table.Sort(Folders,{"Date created", Order.Descending})
in
Sorted
And the result:
Now that you’ve got through the M code, it would be really easy to apply any masking option to this. Here are couple of examples:
1- Fetch Only Folders Created after Specific Date
let
Source = Folder.Contents("C:\Users\Reza\Dropbox\Speaking"),
TypeAdded=Table.AddColumn(Source,"Type",each Value.Is([Content],type table)),
Folders=Table.SelectRows(TypeAdded, each [Type]=true),
Sorted=Table.Sort(Folders,{"Date created", Order.Descending}),
Filtered=Table.SelectRows(Sorted, each [Date created]>DateTime.FromText("2015-1-1"))
in
Filtered
Result
2- Fetch Only Files with .txt extension and name similar to “amp”
let
Source = Folder.Files("C:\Users\Reza\Dropbox\Speaking"),
Sorted=Table.Sort(Source ,{"Date created", Order.Descending}),
Filtered=Table.SelectRows(Sorted, each [Extension]=".txt" and Text.Contains([Name],"amp"))
in
Filtered
Result:
Published Date : July 31, 2015
Joining tables is not a new concept, I bet all readers of my blog at least have a clue about that. However there are different types of joins, and applying these types of Joins are not all possible through Power Query GUI. Power BI recently took an step and implemented that in the GUI, however you might like to know how to apply that in the Power Query. The trick is that M is your friend, You can do whatever you want behind the scenes with M script.
I don’t want to go through the details of explaining every join type here. Picture below illustrated it perfectly;
Picture referenced from: http://www.udel.edu/evelyn/SQL-Class2/SQLclass2_Join.html
Now let’s see how to use joins through Power BI and Power Query;
In Power BI Desktop you can join two tables with Merge menu item in the Query Editor, in Home tab, Under Combine, Merge Queries.
The Merge Window will appear with ability to select first table (Left part of the join), and the second table (Right part of the join). You can choose columns that you want to participate as joining key within an order (you can choose multiple columns with Ctrl Key). And there is join kind that you can choose.
The default behavior is left outer join, which means all records from the first table, if there is any record in the first table that matches record(s) in the second table it would be listed as well.
After joining tables, the second table will appear as a field that has table value in it’s cells. What you need to do is to select columns that you want to show in the result set.
Choose columns as below:
You can also choose from one of other join types as mentioned below:
At the time of writing this blog post Power Query Editor (GUI) only supports two types of joins mentioned above: Left Join, and Inner Join.
You should follow the same path through Merge Queries, and then you will see joining options as below:
As you see the default behavior is Left join. You can change it to inner join with selecting “Only include matching rows”.
You can apply any join type that you want simply by going to advanced editor, and changing the M script as below:
Go to View tab, and click on Advanced Editor:
In the Advanced Editor query window you can see the M script that builds the result set. Find the join function and change the JoinKind.
JoinKind is an enumeration type that can have below values:
So you can simply change it as you want.
This feature I reckon soon will be available on Power Query Editor GUI as well, but till that time the above description hopefully help you in any situation that you want to set a join type.
Published Date : July 30, 2015
Yesterday in NZ BI user group meeting, I’ve been asked that does Power Query custom functions return only one value as the result set? or they can return multiple values. I’ve answered Yes, and I’ve explained that through a sentence how to do it with Records, List, or Table. Then I thought this might be a question of many people out there. So I’ve wrote this blog post to illustrate how to return multiple values from a custom function in Power Query.
If you don’t know how to create a custom function, please read my other blog post with an example of Day Number of Year function for Power Query. In this post I’ll show you through an example of how to return multiple results from a Power Query function.
As you probably know Power Query function return single value by default, and that is the value result of the operation in the “in” clause of the function. Now how to return multiple values? Simply by returning different type of object. The trick is that Power Query custom function can return any single object. and that object can be simple structure object such as Date, Text, Number. Or it can be multiple value objects such as Record, List, and Table.
To understand difference between Record, List, and Table:
)Above objects can hold multiple values. So the only thing you need to do in return one of above objects based on your requirement. In example below I’ve returned a Record as a result set, but you can do it with other two data types.
As an example I would like to write a function that fetches both first and last date of a month, the input parameter of this function is a date value with text data types, such as “30/07/2015″.
** Note that Date Conversion function is locale dependent. So if the date time of your system is no DD/MM/YYYY then you have to enter date as it formatted in your system (look below the clock on right hand side bottom of your monitor to check the format).
Let’s start by calculation of First Date of the Month
We need to fetch the year, and the month, and then built a date string for first day (day 1) of that month and year, and finally convert it to Date datatype.
Here is the script:
let
FirstAndLastDayOfTheMonth = (date) => //function definition
let
dated=Date.FromText(date),//convert input text to Date
year=Date.Year(dated),//fetch year
month=Date.Month(dated),//fetch month
FirstDateText=Text.From(year)&"-"&Text.From(month)&"-01",//generate text value of the first date
FirstDate=Date.FromText(FirstDateText)//convert text value to date
in
FirstDate//return result of the function
in
FirstAndLastDayOfTheMonth("30/07/2015")//function call
and the Result:
For fetching last date of the month, we use same method of first date, except one change. which is the day part of the calculation should be number of days in the month, which comes from Date.DaysInMonth function.
Here is the script:
let
FirstAndLastDayOfTheMonth = (date) =>
let
dated=Date.FromText(date),
year=Date.Year(dated),
month=Date.Month(dated),
FirstDateText=Text.From(year)&"-"&Text.From(month)&"-01",
FirstDate=Date.FromText(FirstDateText),
daysInMonth=Date.DaysInMonth(dated),//fetch number of days in month
LastDateText=Text.From(year)&"-"&Text.From(month)&"-"&Text.From(daysInMonth),
LastDate=Date.FromText(LastDateText)
in
LastDate
in
FirstAndLastDayOfTheMonth("30/07/2015")
and the result:
Now we have both values and we want to return them both. I’ll create an empty record first. Empty record can be created simply with this : [] .
Then I used Record.AddField function to add fields one by one. Record.AddField gets three parameters: the record that field will be added to it, name of the new field, and value of the new field.
Here is the script:
let
FirstAndLastDayOfTheMonth = (date) =>
let
dated=Date.FromText(date),
year=Date.Year(dated),
month=Date.Month(dated),
FirstDateText=Text.From(year)&"-"&Text.From(month)&"-01",
FirstDate=Date.FromText(FirstDateText),
daysInMonth=Date.DaysInMonth(dated),
LastDateText=Text.From(year)&"-"&Text.From(month)&"-"&Text.From(daysInMonth),
LastDate=Date.FromText(LastDateText),
record=Record.AddField([],"First Date of Month",FirstDate),
resultset=Record.AddField(record,"Last Date of Month",LastDate)
in
resultset
in
FirstAndLastDayOfTheMonth("30/07/2015")
and the result:
How to access this Record’s values
As you see in above the function returns a record with two fields. now we can access fields by name, with script like this:
FirstAndLastDayOfTheMonth("30/07/2015")[Last Date of Month]
and the result would be a single value
Here is the full code of example if you want to try it yourself.
You can have another record in a field’s value, you can have a list in a field’s value, and you can have a table in a field’s value. so you can create any data structure that you want as the result set of your function.
Published Date : July 29, 2015
You’ve heard the news about Power BI Desktop release with bunch of new features. If you don’t, read blog post here to understand new features of Power BI. Waterfall chart is one of the new visualization elements in this product. For finance people this chart makes sense, but not everyone knows the usage of that. In this post I want to explain what is Waterfall chart, when to use it? and how to use it? So you would learn what’s Waterfall chart all about?
There are many charts that are self-explanatory, such as Bar chart, line chart…. Some charts might need a little bit working with to understand what is the best situation to use them, such as scatter chart (especially with play dimension). Some charts looks easy but you might not get their reason to exists, and the type of problem that they solve. From feedback that I’ve heard I think waterfall chart is in the latest category. But don’t be afraid, it is an easy chart. you only need to know when and how to use it.
Waterfall Chart is a type of chart that usually used for
“understanding the cumulative effect of sequentially introduced positive or negative values”.
Don’t blame me! I’ve get that definition from Wikipedia My simple definition however is: Waterfall chart is for analysis of up and downs for an additive measure. as an example you can analysis cashflow based on up and downs of it through all months of a financial year.
You can also use waterfall chart for analysis of inventory, credit, performance, and everything else that falling or raising of its value matters. sequential of attribute that describe the value is also another helpful dimension of the waterfall chart. For example you want to know up and downs of performance through the last year with the sequence of quarters that you’ve done performance reviews.
So Don’t limit yourself, this chart visualize and tell many stories, just think big and find situations that you can use this chart to respond to the existing requirement.
Easy! When you know the usage of chart, and you have the data set to work with, then just use Waterfall chart in Power BI Desktop. I’ll show it through an example to you.
I used the data set provided by Debra Dalgleish in this post. The data set is a cashflow data set as below:
As you see in above data set we have periods (months), base, up, down, and the cash flow. If you want to understand how the calculation of cashflow works. it is as below:
So Let’s use this data set in the Power BI.
I just fetch the data from the excel file as is. with no changes. the only change I make is to add an Index Column to it (this index column will be used for sorting later on);
After adding the index column and changing name of the query to CashFlow. I’ll start preparing the model.
There is only one data table in our model, so modelling shouldn’t be a problem. However there are some changes that I like to make as below:
For doing above change; select Period column first, then from Modeling tab, click on Sort By Column, and then choose Index. If you don’t apply this change waterfall chart will sort periods based on their alphabetical order.
Go to Report tab. double click on Waterfall chart from Visualizations pane in the right hand side.
You have to set only two parameters:
As you see in above chart, the cashflow and its up and down showed beautifully in the chart. Order of period category is also right, and that is because of the Sort By Column setting we’ve done in previous section.
You can apply formatting as you want to this chart, like changing colors, backgrounds, title and so on. I just like to set data labels for now.
And this is the final chart (with few formatting):
Published Date : July 28, 2015
There are number of Date and DateTime built-in functions in Power Query which are really helpful. There is also a function for DayNumberOfYear. However I’ve thought it would be a good example to go through writing a function that uses Generators, Each singleton function, and error handling all inside a custom function. Through this post you will also learn;
Let’s consider this date as today’s date : 28th of July of 2015 (this is actually the date of this blog post)
There might be number of methods to calculate the day number of year for this date (which is 209). I just use one of them here. Steps are as below;
Some of calculations can be helped through with Power Query Date functions. So let’s start;
1 – Create a function in Power Query called DayNumberOfYear as below
If you don’t know where to write below code:
let //start of the code
DayNumberOfYear= (date) => //Function name, and input parameter
let //start of function body
dated=DateTime.FromText(date) //date conversion from text
in //start of function output
dated //function body output
in //start of output lines generated
DayNumberOfYear("07/28/2015") //call function by a value
I’ve put some comments in above script to help you understand each line. In general DayNumberOfYear is name of the function. It accepts an input parameter “date”. and convert the parameter from text value to DateTime. the last line of the code calls the function with specific date (“07/28/2015″).
** Note that Date Conversion function is locale dependent. So if the date time of your system is no MM/DD/YYYY then you have to enter date as it formatted in your system (look below the clock on right hand side bottom of your monitor to check the format).
the result of above script will be:
07/28/2015 12:00:00 a.m.
2 – Fetch Month Number and generate a list of all prior months.
Fetching month number is easily possible with Date.Month function. the remaining part is looping through months from January of this year to previous month (of the given date). Unfortunately there is no loop structure in Power Query M language yet, but fortunately we can use Generator functions for that. Generator function is a function that produce/generate a list based on some parameters. For example you can generate a list of dates from a start date, based on given occurrence of a period of time. or you can generate a list of numbers. For this example we want to generate list of numbers, starting from 1 (month January) to the current month number minus 1 (previous month).
Here is the code:
let
DayNumberOfYear= (date) =>
let
dated=DateTime.FromText(date),
month=Date.Month(dated),//month number
MonthList=List.Numbers(1,month-1) // generate list of months from Jan to previous month
in
MonthList
in
DayNumberOfYear("07/28/2015")
The result is a list of month numbers as below:
Generator Function used in above code is List.Numbers. this function generate list of numbers starting from a value.
3 – Transform list to full date list
We have to calculate number of days for each month in the function. Number of days in each month can be fetched by Date.DaysInMonth function. However this function accept a full DateTime data type, and the value that we have in our list members are text. so we have to produce a datetime value from it. for generating a full date we need the year portion as well, we use Date.Year function to fetch that.
Here is the code to transform the list:
let
DayNumberOfYear= (date) =>
let
dated=DateTime.FromText(date),
month=Date.Month(dated),
MonthList=List.Numbers(1,month-1),
year=Date.Year(dated), // fetch year
TransformedMonthList=List.Transform // transform list
(MonthList,
each Text.From(year)&"-"&Text.From(_)&"-1") // generate full text value
in
TransformedMonthList
in
DayNumberOfYear("07/28/2015")
The result is:
As you see the above result is not still of datetime data type, we’ve only generated full date as text value. Now we can convert values to datetime data type
Here is the code:
let
DayNumberOfYear= (date) =>
let
dated=DateTime.FromText(date),
month=Date.Month(dated),
MonthList=List.Numbers(1,month-1),
year=Date.Year(dated),
TransformedMonthList=List.Transform
(MonthList,
each Text.From(year)&"-"&Text.From(_)&"-1"),
DateList=List.Transform(
TransformedMonthList,
each DateTime.FromText(_))//transform to DateTime value
in
DateList
in
DayNumberOfYear("07/28/2015")
and the result:
4 – Transform the list to List of DaysNumberOfMonths
We use DaysInMonth function to fetch number of days in each month from the list. here is the code:
let
DayNumberOfYear= (date) =>
let
dated=DateTime.FromText(date),
month=Date.Month(dated),
MonthList=List.Numbers(1,month-1),
year=Date.Year(dated),
TransformedMonthList=List.Transform
(MonthList,
each Text.From(year)&"-"&Text.From(_)&"-1"),
DateList=List.Transform(
TransformedMonthList,
each DateTime.FromText(_)),
DaysList=List.Transform(
DateList,
each Date.DaysInMonth(_))
in
DaysList
in
DayNumberOfYear("07/28/2015")
and the result:
5 – Calculate Sum of Dates and Add day number of this month to it
The list is ready to use, we need to sum it up only. And then add the current day Date.Day from the given date to it.
Here is the code:
let
DayNumberOfYear= (date) =>
let
dated=DateTime.FromText(date),
month=Date.Month(dated),
MonthList=List.Numbers(1,month-1),
year=Date.Year(dated),
TransformedMonthList=List.Transform
(MonthList,
each Text.From(year)&"-"&Text.From(_)&"-1"),
DateList=List.Transform(
TransformedMonthList,
each DateTime.FromText(_)),
DaysList=List.Transform(
DateList,
each Date.DaysInMonth(_))
in
List.Sum(DaysList)//sum of values in the list
+Date.Day(dated)//current date's day number
in
DayNumberOfYear("07/28/2015")
and the result is:
6- Error Handling
The function is working, but if the given date format be wrong the we will face and error such as below:
So let’s add few lines of error handling to the code. We can simply use Try clause to the code as below:
let
DayNumberOfYear= (date) =>
let
dated= try DateTime.FromText(date),
month=Date.Month(dated[Value]),
MonthList=List.Numbers(1,month-1),
year=Date.Year(dated[Value]),
TransformedMonthList=List.Transform
(MonthList,
each Text.From(year)&"-"&Text.From(_)&"-1"),
DateList=List.Transform(
TransformedMonthList,
each DateTime.FromText(_)),
DaysList=List.Transform(
DateList,
each Date.DaysInMonth(_))
in
if dated[HasError]
then dated[Error]
else List.Sum(DaysList)+Date.Day(dated[Value])
in
DayNumberOfYear("707/28/2015")
Result for bad formatted given date is :
Here is the full code of the script:
let
DayNumberOfYear= (date) =>
let
dated= try DateTime.FromText(date),
month=Date.Month(dated[Value]),
MonthList=List.Numbers(1,month-1),
year=Date.Year(dated[Value]),
TransformedMonthList=List.Transform
(MonthList,
each Text.From(year)&"-"&Text.From(_)&"-1"),
DateList=List.Transform(
TransformedMonthList,
each DateTime.FromText(_)),
DaysList=List.Transform(
DateList,
each Date.DaysInMonth(_))
in
if dated[HasError]
then dated[Error]
else List.Sum(DaysList)+Date.Day(dated[Value])
in
DayNumberOfYear("707/28/2015")
In this post you’ve learned: