Ebooks Amazon Free

FREE ALL EBOOKS ON AMAZON AND COURSES ON UDEMY- What books and courses do you need? We have them FREE for you...

Showing posts with label Power Query. Show all posts
Showing posts with label Power Query. Show all posts

03 August, 2020

Collect, Transform and Combine Data using Power BI and Power Query in Excel (Business Skills)

Collect, Transform and Combine Data using Power BI and Power Query in Excel (Business Skills)

Gil Raviv
Using Power Query, you can import, reshape, and cleanse any data from a simple interface, so you can mine that data for all of its hidden insights. Power Query is embedded in Excel, Power BI, and other Microsoft products, and leading Power Query expert Gil Raviv will help you make the most of it. Discover how to eliminate time-consuming manual data preparation, solve common problems, avoid pitfalls, and more. Then, walk through several complete analytics challenges, and integrate all your skills in a realistic chapter-length final project. By the time you're finished, you'll be ready to wrangle any data-and transform it into actionable knowledge. Prepare and analyze your data the easy way, with Power Query - Quickly prepare data for analysis with Power Query in Excel (also known as Get & Transform) and in Power BI - Solve common data preparation problems with a few mouse clicks and simple formula edits - Combine data from multiple sources, multiple queries, and mismatched tables - Master basic and advanced techniques for unpivoting tables - Customize transformations and build flexible data mashups with the M formula language - Address collaboration challenges with Power Query - Gain crucial insights into text feeds - Streamline complex social network analytics so you can do it yourself For all information workers, analysts, and any Excel user who wants to solve their own business intelligence problems.
Microsoft Press
402 / 543
ISBN 10:
ISBN 13:
Business Skills
PDF, 33.53 MB
Save for later

Read online bellow⏬

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

22 July, 2020

Learn Power Query: A low-code approach to connect and transform data from multiple sources for Power BI and Excel

Book cover Learn Power Query: A low-code approach to connect and transform data from multiple sources for Power BI and Excel

Learn Power Query: A low-code approach to connect and transform data from multiple sources for Power BI and Excel

Linda FoulkesWarren Sparrow
Discover how you can combine data from various sources to create data models to suit your business requirements with the help of this clear and concise guide
Key Features
  • Understand how Power Query overcomes the shortcomings of Excel Power Pivot in handling complex data
  • Create customized dashboards and multi-dimensional reports using Power Query and Power BI
  • Learn the Power Query M language and write advanced queries using custom functions
Book Description
Power Query is a data connection technology that allows you to connect, combine, and refine data from multiple sources to meet your business analysis requirements. With this Power Query book, you'll be empowered to work with a variety of data sources to create interactive reports and dashboards using Excel and Power BI.
You'll start by learning how to access Power Query across different versions of Excel and install the Power BI engine. After you've explored Power Pivot, you'll see why Excel users find it challenging to clean data in Power Pivot and learn how Power Query can help to tackle the problem. The book will show you how to transform data using the Query Editor and write functions in Power Query. A dedicated section will focus on functions such as IF, Index, and Modulo, and creating parameters to alter query paths in a table. You'll also work with dashboards, get to grips with multi-dimensional reporting, and create automated reports. As you advance, you'll cover the M formula language in Power Query, delve into the basic M syntax, and write the M query language with the help of examples such as loading all library functions offline in Excel and Power BI. Finally, the book will demonstrate the difference between M and DAX and show how results are produced in M.
By the end of this book, you'll be ready to create impressive dashboards and multi-dimensional reports in Power Query and turn data into valuable insights.
What you will learn
  • Convert worksheet data into a table format ready for query output
  • Create a dynamic connection between an Access database and Excel workbook
  • Reshape tabular data by altering rows, columns, and tables using various Power Query tools
  • Create new columns automatically from filenames and sheet tabs, along with multiple Excel data files
  • Streamline and automate reports from multiple sources
  • Explore different customization options to get the most out of your dashboards
  • Understand the difference between the DAX language and Power Query's M language
Who this book is for
This Power Query book is for business analysts, data analysts, BI professionals, and Excel users looking to take their skills to the next level by learning how to collect, combine, and transform data into insights using Power Query. Working knowledge of Excel and experience in constructing and troubleshooting Excel formulas and functions is expected.
Table of Contents
  1. Installation and Setup
  2. Power Pivot Basics, Inadequacies, and Data Management
  3. Introduction to the Power Query Interface
  4. Connecting to Various Data Sources using Get and Transform
  5. Transforming Power Query Data
  6. Advanced Power Queries and Functions
  7. Automating Reports in Power Query
  8. Creating Dashboards with Power Query
  9. Working with M
  10. Examples of M Usage
  11. Creating a Basic Custom Function
  12. Differences between DAX and M
Packt Publishing
ISBN 10:
ISBN 13:
PDF, 44.71 MB
Save for later

Read online bellow⏬

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

19 February, 2020

[Free ebook]Power Query for Power BI and Excel-Chris Webb

[Free ebook]Power Query for Power BI and Excel-Chris Webb



Product description

Power Query for Power BI and Excel is a book for people who are tired of copying and pasting data into Excel worksheets. Power Query, part of the Microsoft Power BI suite, is a tool that automates the process of getting data into Excel and will save you hours of dull, repetitive, and error-prone work! Power Query makes it easy to extract data from many different data sources, filter that data, aggregate it, clean it and perform calculations on it, finally loading that data into either your worksheet or directly into the new Excel 2013 Data Model used by Power Pivot. This concise, practical book provides a complete guide to Power Query and how to use it to solve all of your Excel data-loading problems.
Power Query for Power BI and Excel goes well beyond the surface of what Power Query can do. The book goes deep into the underlying M language, showing you how to do amazing things that aren’t going to be possible from just the GUI interface that is covered in most other books. You’ll have full command of the GUI, and you’ll be able to drop into the M language to go beyond what the GUI provides. The depth in this book makes it a must-have item for anyone who is pushing Power BI and Excel to their limits in the pursuit of business intelligence from data analysis.
  • Teaches the basics of using Power Query to load data into Excel
  • Helps you solve common, data-related problems with Power Query
  • Shows how to write your own solutions in the powerful M language

About the Author

Chris Webb is a UK-based consultant and trainer specializing in the Microsoft Business Intelligence stack. He has co-authored three books - MDX Solutions, Expert Cube Development with Microsoft SQL Server Analysis Services 2008, and SQL Server Analysis Services 2012: The BISM Tabular Model and is a regular speaker at conferences and user groups around the world. He blogs at http://cwebbbi.wordpress.com.

Features & details

Product information

#eama #evba #etipfree

28 January, 2020

5 tips: Power Query for Accountants (and finance people)

5 tips: Power Query for Accountants (and finance people)

Do you work with accounting or finance data? You are going to love, no scratch that, adore Power Query. It can save you precious time, make you look like a hero in-front of clients and keep you sane.
In this masterclass, learn all about Power Query for accountants (and other kinds of finance people) & 5 tips.
This is part of Power Mondays series, where every Monday we discuss Power BI / Power Query / Power Pivot.

Why Power Query?

Power Query is a data processing software. Using Power Query, you can create process driven programs (queries) to collect data, clean or reshape it, calculate things and publish final output as a table. Once you create a query in PQ, when the underlying data changes, you just refresh the query and brand new data will be fetched, cleaned and published for you.
For example, you can use Power Query to combine ledger files from various accounts to one master file with Power Query. No more VBA or manual copy pasting. Just one query and you are done.

In this lesson

This masterclass is broken in to 5 topics.

What is Power Query – Quick intro

We start by discussing what Power Query is, how to access it, understanding Power Query UI, how to enable formula bar and what various ribbons are.

Extracting data from bank statement

You will learn how to
  1. Connect to a file on computer
  2. Load bank transactions to a new Power Query connection
  3. Delete transactions of certain type
  4. How to categorize transactions by amount – small, medium or large txns
  5. How to duplicate a query
  6. Splitting transactions in to debits and credits

Combining monthly ledger data files to single table

In this section, learn how to
  1. Connect to a folder full of similar format files
  2. Set up sample query to teach Power Query how you want to merge files
  3. Combine the data and publish
  4. Create a pivot table report from the data
  5. Update the folder and refresh
  6. Look at modified pivot report

Reconciling Sent vs. Paid invoices

In this topic,
  1. What is reconciliation
  2. Loading tabular data in to Power Query
  3. Connecting two tables using “Merge queries” option
  4. Using left joins
  5. Removing duplicates
  6. Tagging invoices as either “Paid in full”, “Under paid”, “Over paid” or “Unpaid” using conditional column feature
  7. Publishing results

Live exchange rate tracker

In this section, learn how to:
  1. Connect to public websites to fetch currency data
  2. Use Reserve Bank New Zealand’s currency rate data as connection
  3. Reshaping data with “Transpose” and “Use headers as first row” options
  4. Removing un-necessary columns
  5. Publishing final exchange rate

Watch Power Query for Accountants – Masterclass

Download Sample files for this lesson

Click here to download sample files for this lesson. The example file is crafted with love and care. Check out the queries by going to Power Query screens. Go thru the steps and enable formula bar to learn what is going on.
How to view / edit the queries?
  1. Go to Data Ribbon
  2. Click on “Show Queries”
  3. Right click on any query and “Edit”
How to refresh the queries?
  1. Go to Data Ribbon
  2. Click on “Refresh All”
  3. Done.
I get an error when refreshing…
  1. The queries are mapped to local folder files
  2. So you need to re-map them to folders on your computer
  3. Edit the query with error (see steps above)
  4. Go to Source step
  5. Click on gear icon
  6. Change file address as per your computer
  7. Save & load the query

AND(You=”Accountant”, Use=”Power Query”)? Let’s talk

Are you an accountant (or finance pro) and use PQ often? Please share your tips, thoughts and ideas in comments section so I can learn from you. Getting on the Power Query bandwagon? Check out the intro to PQ and this lesson to get going. Post your wins in the comments.
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

20 December, 2019

Download the Commonwealth games medal tracker

The 2018 edition of Commonwealth games are on for a week now. Both of my homes – India and New Zealand have done so well. Naturally, I wanted to gather games data and make something fun and creative from it. Here is my attempt to amuse you on this Friday.
Looks interesting? Want to know how to make something like this on your own? Then read on…

1. Gather data thru live connection to gc2018.com

We want to set up a refreshable visualization. So the data will be fetched thru PowerQuery. All we need to know about medal standings, medalists and country participation data is available at gc2018.com website.
Latest Medal Standings: This is available at https://results.gc2018.com/en/all-sports/medal-standings.htm page as an HTML table (the first table on the page).
So we can get the data using below M:
= Web.Page(Web.Contents("https://results.gc2018.com/en/all-sports/medal-standings.htm")){0}[Data]
This is extracting data column of first row of the Web.Page.
Number of participants by country: There is no one page where this information is available. Instead you need to visit each country’s page on gc2018.com to get the data from participants table. For example, the page for Bermuda (available at https://results.gc2018.com/en/all-sports/entries-bermuda.htm) looks like this:
Fortunately, all URLs follow the same pattern. https://results.gc2018.com/en/all-sports/entries-<country name>.htm
So given a URL in column [URL], we can use this custom column formula to get the total number of participants.
=Table.SelectRows(Web.Page(Web.Contents([URL])){0}[Data], each ([Discipline] = "Total")))
This is extracting the first table on URL and then filtering it for Total Row. 
Arranging everything in one table:
We can use a bit of built-in operations in Power Query to arrange all the necessary data in one tidy table. I am not going to explain all steps, but here is the final output. Try to come up with this on your own.
Now that our medal data is in Excel, in a table named medal_standings, let’s go ahead to next step.

2. Calculations to show medal standings by any criteria

The calculation engine for our little medal standings has few key things:
  • Fetching and sorting by a column
  • Slicer selection for sort options (Gold, Silver, Bronze, Total Medals, M/P)
Let’s go thru them:
Fetching and sorting by a column:
We would like to see countries by Gold, Silver, Bronze, Total medals and Medals per participant.
As per our medal_standings table, these are 3,4,5,6 and 9 respectively.
Assuming the column we want to sort is given by a named range – sort.option.num, we can use INDEX formula to fetch values, like this:
=IFERROR(INDEX(medal_standings,<row number>,sort.option.num),-10)
The -10 ensures that if we poll for a row that doesn’t exit, we get a negative value rather than 0. As some countries have 0 medals, having negative ensures that when sorting such rows are always at bottom.
Once we fetch a column, you can use LARGE() to re-order them top to bottom.
As there will be ties (few countries getting same number of medals), we can use de-duplication logic. This is when you add a very small unique fraction to each row before calling LARGE(). It is an elegant way to deal with ties and overcome Excel’s lookup formula limitations of returning only first match. See this decade old post by Robert discussing deduplication technique.
After this, just re-arrange original data (only columns needed for output) using another set of INDEX formulas.
A slicer to allow user to pick sort option
Now let’s just link up sort.option.num to a slicer so user can tell calculation engine what the sort order should be.
Start by making a pivot from a range like this:
But when you add a slicer on sort option, you realize the folly of your plan. The slicer buttons are out of order.
Technically, they are in order – alphabetical. But that is not what we want. We want them in the order – Gold, Silver, Bronze, Total Medals and M/P.
So what now?
Simple, we can ask Jackie Chan to karate chop the slicer and re-arrange it.
Alas, my summonJackie() macro was subscript out of ranging. So we need something else.
So we cheat Excel. We can pre-fix empty spaces – CHAR(129) to the slicer items. Since these are empty spaces, we just add 1 space for Gold, 2 for Silver etc. and make a slicer from these new values.
Note: In Power BI, you can simply order the sort option column by index number and that will fix the slicer problem. In fact, we wouldn’t bother with a slicer as Power BI tables are sortable by clicking on header.
That is better. Now simply style it and give it a buzz cut and you get this.

3. Preparing the viz

Now that everything we need is ready, simply bring calculated table to a blank worksheet (using Copy, Paste links) and arrange it in a neat table. Add Conditional formatting > Databars on medal and M/P columns. Position slicer neatly where these columns headers should be and you are gold.

Every now and then press Ctrl+Alt+F5 and go make a cuppa. When you are back, the medal table would be updated. Of course, come 16th of April 2018, there is no need to refresh it as the games would have ended.

Download the Commonwealth 2018 games medal tracker

Click here to download the Excel file. Play with it to learn more. Examine the query definition, control sheet and viz sheet to understand how it is put together. Make changes to the query (but duplicate it first, otherwise you will break the calculations) to fetch other data and make your own charts.

Ways to enhance this – adding past performance etc.

You can use the data from https://thecgf.com/ (Commonwealth Games Federation) to see historical performance and contestant data. They do not yet have 2018 values (as the games are ongoing) but you can see how countries have done in 2014 or 2010. Or you could combine this with performance in Olympics. How about combining this with demographic and well-being data (Gini scores or HDI ranks)? There are several ways you can mash-up this.
#evba #etipfree
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Duplicate Columns Using The Power Query Edit

Duplicate Columns Using The Power Query Editor

Suppose you have the source data as shown below. Here, the marked column is the one that we want to duplicate.

Step 1: Select the Column that you want to duplicate

After you load the data source into the Power Query Editor, you have to find the column that you wish to duplicate and select the same. As shown in the picture below, we are going to duplicate the salary column.

Step 2: Duplicate the selected column

When you select the column to be duplicated, go ahead to do the following changes on the ribbon.
Go to Add Column tab >Duplicate Column option

Step 3: Save Data

After you duplicate the column you get the result immediately on the screen of the Power Query Editor. When done with your work, just click on the save icon on the top to save the changes for next time.
#evba #etipfree
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1