Подготовка данных к анализу с помощью запросов

1.

Занятие 2
Продолжаем готовить данные
к анализу с помощью запросов

2.

Цели занятия

3.

Цели занятия
В конце занятия вы сможете:
Преобразовывать исходные данные, приводя
их к нужному виду

4.

О чём поговорим, что
сделаем

5.

План занятия
Закончим изучать начатые на предыдущем занятии преобразования таблиц «вцелом»
Научимся менять содержимое существующих столбцов
Научимся добавлять новые столбцы на основе существующих

6.

Вспомним, о чем мы
говорили на прошлом
занятии

7.

Что такое «табличный вид»

8.

Какие преобразования доступны в Power Query
1.Преобразования
таблицы "в целом"
1.Добавление
новых столбцов
на основе старых
1.Изменение
содержимого
столбцов

9.

Преобразования таблицы
«в целом»

10.

Задача
На прошлом занятии мы видели несколько
«нетаблиц». Пришло время сделать из них
нормальные таблицы
Для этого подключимся к файлу Excel «Исправляем
нетаблицы.xlsx»

11.

Исправляем нетаблицу1
Нетаблица 1. Что в ней нужно
исправить:
1.
В столбце [Продажи]
заменить:
• «Мруб.» на «000000»;
• « тыс. шт.» на «000»;
2. [Продажи] разбить на 2
столбца
3. Строки с несколькими
месяцами разбить по кол-ву
месяцев
Месяц
Янв
Фев, мар
Апр
Май
Июн, авг, дек
Июл
Сен
Окт
Ноя
Продажи
13Mруб., 11 тыс. шт.
15Mруб., 14 тыс. шт.
14Mруб., 10 тыс. шт.
11Mруб., 10 тыс. шт.
10Mруб., 13 тыс. шт.
15Mруб., 14 тыс. шт.
14Mруб., 15 тыс. шт.
12Mруб., 12 тыс. шт.
14Mруб., 10 тыс. шт.

12.

Исправляем нетаблицу2
Нетаблица 2. Пусть нам нужны
только суммарные показатели по
типам. Что в ней нужно исправить.
1.
Сгруппировать по [Тип], с
суммой по [Выручка] и
[Количество]
2. Отфильтровать [Тип]<> «Все
типы»

13.

Исправляем нетаблицу3
Нетаблица 3. Что в ней нужно исправить.
1.
Превратить матрицу в таблицу

14.

Как матрица превращается в таблицу

15.

Исправляем нетаблицу4
Нетаблица 4. Что в ней нужно
исправить.
1.
Разнести значения из [Значение]
по двум столбцам – [Выручка] и
[Кол-во]

16.

Исправляем нетаблицу5
Нетаблица 5. Как будем исправлять.
1.
2.
3.
4.
5.
6.
7.
Объединим столбцы [Филиал] и
[Тип]
Транспонируем таблицу
Заполним пропуске в столбце с
годами
Первую строку превратим в заголовок столбцов
Разнесем значения по отдельным столбцам выручку и
количество
Разобьем обратно столбец из п. 1
Присвоим нужные имена столбцам

17.

Преобразования
существующих столбцов и
добавление новых

18.

Задача
Мы – аналитики в магазине, продающем расходные
материалы через интернет. Нам нужно подготовить
данные по продажам для дальнейшего анализа.
Файл «Данные магазина по продажам.xlsx»

19.

Данные по продажам магазина расходных материалов
Что должно быть в результате:
1. Имена написаны правильно;
2. Данные нужны только по женщинам-ИП, разместившим заявки более 100
дней назад в выходные;
3. Нужен дополнительный столбец с ценой 1 единицы товара без НДС с
точностью до копейки с указанием размерности (т.е. «ХХХ руб. за шт.»);
4. Оставшиеся строки должны быть пронумерованы с шагом 10 с 10;
5. Все покупательницы должны быть разбиты на 3 группы:
- VIP: те, у кого цена за единицу не меньше 2000 руб.
- Стандартные: те, у кого цена за единицу не меньше 1000, но не VIP
- Остальные

20.

Чему мы сегодня
научились

21.

Резюме
− Делать разнообразные манипуляции с данными
− И даже чуть-чуть анализировать данные

22.

Резюме
Но, чего мы еще пока не умеем:
• Делать сложные преобразования, включающие в
себя одновременно разные запросы
Но через неделю уже будем иметь!

23.

Полезные материалы

24.

Полезные материалы
− Документация по Power BI Desktop
− Сообщество (англ.)
− Блог Microsoft Power BI

25.

Про домашние задания

26.

Про домашние задания
1. Читайте, пожалуйста, внимательно задание. Очень внимательно;
2. Выполняйте, пожалуйста, все условия задания в точности так, как они
сформулированы. Не срезайте углы;
3. Если что-то непонятно как делать – пересмотрите материалы занятия. Для
выполнения задания не требуется ничего, чего нет в материалах занятия;
4. Домашние задания нужно делать. Это самая главная часть обучения. Можно
научиться только делая задания и не просматривая материалы. Но нельзя научиться
только просматривая материалы;
5. После задания почти всегда есть скриншот с требуемым результатом. Если Ваш
результат отличается от скриншота – доработайте свой результат;
6. Если Вы не укладываетесь в срок сдачи, можно запросить продление срока. Но
лучше укладываться в срок;
7. Срок решения – не оценивается. Качество решения – оценивается. Поэтому лучше
сдать решение позже, но качественное;
8. При поиске решения помните, что решение должно быть универсальным: оно должно
правильно работать и с другими исходными данными.

27.

Домашнее задание 1
Установить истину в
вопросе дорог в России

28.

Домашнее задание 1
Задача
Вы обсуждаете с другими пользователями в группе FB состояние дорог
в РФ. В группе не могут прийти к согласию о том, в каком регионе
больше всего перекрытых дорог (по длине). Вы решили исследовать этот
вопрос. Для этого Вы нашли на сайте Росавдора таблицу о перекрытии
дорог.
Постройте рейтинг регионов по убыванию длины перекрытых дорог.
Примечания: 1. Если при открытии файла будут непонятные символы,
используйте кодировку 1251: Кириллическая (Windows)
2. Строки, где начало или конец перекрытого участка не указан (стоит
прочерк) в рейтинге, не используем;
3. Будьте внимательны ближе к концу запроса.
Домашнее задание 1

29.

Домашнее задание 1, результат

30.

Домашнее задание 1
Замечания к домашнему заданию 1
1. В качестве источника данных нужно использовать именно ту ссылку, которая
приведена в условии. Выгружать страницу Википедии в файл и затем подключаться к
этому файлу – не нужно;
2. Длина дороги – это разность между ее концом и началом (либо, наоборот, между
началом и концом);
3. Избавиться от знака минуса перед числом можно разными способами. Лучше всего
это делать с помощью преобразования «Абсолютное значение». Это преобразование
меняет знак у отрицательного числа и оставляет неизменным положительное число;
4. Чтобы посчитать какой-то обобщающий показатель (сумму, среднее и т.п.) для
нескольких строк, нужно сделать группировку по столбцу, содержащему одно и то же
значение для всех этих строк, и выбрать по какому столбцу и какой именно
показатель считать.

31.

Домашнее задание 2
Закрепляем наши умения
по манипулированию с
данными

32.

Домашнее задание 2
Задача
Вы работаете в компании, разрабатывающей мобильные приложения.
Ваш коллега прислали вам файл «Отчет по приложению.xlsx». К
сожалению, формат данных в этом файле не позволяет сразу начать
работу с этим отчетом. Вам нужно привести данные к правильному
формату.
Что нужно сделать?
1. Привести исходные данные к виду таблицы
2. Сделать цифровые показатели считаемыми

33.

Домашнее задание 2, результат

34.

Домашнее задание 2
Замечания к домашнему заданию 2
1. Запись «1,014М» означает число 1 014 000. Фактически, буква «М» означает, что число
перед буквой нужно умножить на 1 000 000;
2. Запись «1,585k» означает число 1 585. Фактически, буква «k» означает, что число
перед буквой нужно умножить на 1 000.

35.

Спасибо за внимание!
Павел Козлов