inside front cover - page intentionally left blank

by

Ken Puls &

Miguel Escobar

Holy Macro! Books

PO Box 541731

Merritt Island, FL 32953

M is for (Data) Monkey

© 2016 Tickling Keys, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and

accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Authors: Ken Puls and Miguel Escobar

Layout: Jill Bee

Copyediting: Kitty Wilson

Technical Editor: Roger Govier

Cover Design: Shannon Mattiza 6’4 Productions

Indexing: Nellie Jay

Ape Illustrations: Walter Agnew Moore

Cover Illustration: Irdan Teras

Published by: Holy Macro! Books, PO Box 541731, Merritt Island FL 32953, USA

Distributed by: Independent Publishers Group, Chicago, IL

First Printing: October, 2015. This edition generated May 3, 2016 with new margins.

ISBN: 978-1-61547-034-1 Print, 978-1-61547-223-9 PDF, 978-1-61547-345-8 ePub, 978-1-61547-034-1 Mobi

LCCN: 2015940635

Table of Contents

Foreword How Power Query Changed Our Lives

Introduction: A New Revolution

Chapter 1 Importing Basic Data

Chapter 2 Overriding Power Query Defaults

Chapter 3 Basic Append Operations

Chapter 4 Importing All Files in a Folder

Chapter 5 Aggregating Excel Worksheets

Chapter 6 Unpivoting Data

Chapter 7 Importing Nondelimited Text Files

Chapter 8 Importing from Databases

Chapter 9 Merging Tables and Queries

Chapter 10 Query Loading Destinations

Chapter 11 Defining Data Types

Chapter 12 Importing Web Data

Chapter 13 Loading Data from Exchange

Chapter 14 Grouping and Summarizing

Chapter 15 Transposing and Unpivoting ­Complex Data

Chapter 16 Automating Refreshing

Chapter 17 Power Query Formulas

Chapter 18 Conditional Logic in Power Query

Chapter 19 Power Query Objects

Chapter 20 Understanding the M Language

Chapter 21 Creating Custom Functions

Chapter 22 Advanced Conditional Logic

Chapter 23 Dynamic Parameter Tables

Chapter 24 Dynamic Calendar Tables

Chapter 25 Query Organization

Index