Седьмой урок из курса Максима Уварова «Power Bi для интернет-маркетинга»

Полезные ссылки к уроку:

• Блог Криса Вебба на аглийском языке
• Переводы некоторых статей Криса Вебба в блоге NeedForData

Видеолекции

Домашнее задание

Рабочий файл: папка l7

Задача

Ваша задача модифицировать вашу bi систему из урока 3, чтобы добавить в нее обновленные и расширенные данные.
В процессе вы будете сталкиваться с ошибками, которые вам предстоит решать.
Удачи!

1. Откройте вашу BI систему с выполненным домашним заданием к уроку 3.
2. Скачайте и распакуйте архив раздатки.
3. Создайте запрос, который получает данные из папки «CSV» из распакованного архива раздатки.
4. Вероятнее всего, power bi автоматически не смог корректно распознать тип данных у столбцов содержащих десятичные дроби (все столбцы с расходами) и даты. У столбцов с десятичными дробями остался тип данных текстовый, а к датам хоть и был применен тип данных «дата», но в строчках ниже появились ячейки с ошибками. Убедиться в этом вы сможете, если примените функцию Keep Errors
5. Чтобы избавиться от ошибок в столбце «Дата» вам нужно удалить автоматически созданный шаг «Changed types» («Измененный тип»), во время которого появились ошибки.
6. Измените тип данных в столбце «Дата», выставив тип данных «Date» с использованием локали «English (USA)» («Английский (США)»).
7. Далее, измените тип данных у всех столбцов с расходом, на формат «decimal number» с локалью «English (USA)» («Английский (США)»). Зажав клавишу ctrl вы можете выбрать несколько столбцов и задать тип данных выделенным столбцам одновременно.
8. После этого вы можете выделить все столбцы и определить типы данных в столбцах автоматически. Чтобы выделить все столбцы сначала выделите первый столбец, зажмите клавишу shift и выберите последний столбец. Либо, выделив любую ячейку в таблице, вы можете нажать на сочетание клавиш CTRL+A. После того как все столбцы выделены вам нужно нажать на кнопку Detect Data types automatically.
9. Убедитесь, что запрос выполняется без ошибок — для этого используйте функцию keep errors.
10. Откройте код текущего запроса (где вы настроили обработку всех файлов из папки CSV), выделите его полностью и скопируйте в буфер обмена.
11. Откройте код запроса Stats в Advanced Editor и замените на код, который вы скопировали на предыдущем шаге. Если у вас нет этого запроса Stats — смотрите урок 3 и разбирайтесь сами, какой запрос вы поименовали не по инструкции.
12. Закройте редактор кода (Advanced Editor), убедитесь, что запрос выполняется корректно.
13. Добавьте столбец «Валовая прибыль». Для этого умножьте столбец «Доход» на 20%.
14. Удалите оригинальный запрос, в котором вы получали данные из папки с CSV.
15. Переименуйте запрос «CampaignNames» в «DimCampaignNames» и включите загрузку запроса в модель данных.
16. Загрузите в модель данных таблицы DimAds и DimPhrases из файла «Dict20190731.xlsx» из архива раздатки.
17. Создайте связи (или сделайте их активными) между таблицами «Stats» и «DimAds» по столбцу «Id объявления», между таблицам «Stats» и «DimPhrases» по столбцу «Id фразы», между таблицами «Stats» и «DimCampaigns» по столбцу «Id кампании». Если вам будут мешать активные связи — удалите или выключите их.
18. Модифицируйте меру, «*Ключевая фраза количество уникальных» по формуле *Ключевая фраза количество уникальных = CALCULATE( DISTINCTCOUNT(DimPhrases[Фраза (ключ)]) ; Stats ). Подробности в видео ниже.
19. Модифицируйте меру средней ставки по аналогии.
20. Восстановите работоспособность визуализаций «Таблиц» в отчете, заменив пропавшие столбцы из таблицы «Stats» на соответствующие столбцы из таблиц «DimCampaignNames» модели данных.
21. Откройте запрос «DimAds» в Power Query.
22. Добавьте «настраиваемый столбец» («custom column») с названием «Uri», в котором введите функцию Uri.Parts с аргументом [URL объявления] (=Uri.Parts([URL объявления]) ).
23. В результате выполнения функции в столбце «Uri» появятся ячейки, содержащие данные в структурированном типе данных «Record» («Запись»). Изучите, чего полезного есть в этих ячейках.
24. Добавьте «настраиваемый столбец» («custom column») с названием «UrlPath». В столбце «UrlPath» должны быть данные из поля «Path» записи в столбце «Uri».
25. Добавьте «настраиваемый столбец» («custom column») с названием «UrlQueryRegion». В столбце «UrlQueryRegion» должны быть данные из поля записи «region» (это поле вложено в поле записи «Query» столбца «Uri»). В уроке я рассказывал как обращаться к несуществующим полям таблицы, чтобы при обращении получалось значение «null» вместо ошибки. Примените эти знания.
26. Удалите столбец «Uri».
27. Загрузите запрос в модель данных.
28. Создайте пустой запрос «TextContainsAny». В Advanced Editor замените код пустого запроса на код функции Text.ContainsAny.pq. Эта функция проверяет, есть ли в тексте из первого аргумента вхождения любого из текстов списка второго аргумента.

/*
//Check if a string contains any of the keywords from a given list
//Usage:
    Text.ContainsAny = Load("Text.ContainsAny"),
    Text.ContainsAny("the cat sat on the mat", {"cat", "apple"})
//Result: true
*/

(str, needles) as logical =>
let
	count = List.Count(needles)
in

List.AnyTrue(
	List.Generate(
		()=>[i=0],
		each [i] < count,
		each [i=[i]+1],
		each Text.Contains(str,needles{[i]})
	)
)

29. В запросе «DimPhrases» разделите столбец «Фраза (ключ)» по крайнему левому разделителю « -» Split Column by Delimeter — Разделить текстовый столбец по разделителю.
30. Переименуйте получившиеся столбцы соответственно «Ключевая фраза» и «Минус-слова».
31. Добавьте столбец «ПродающиеДобавки», в котором при помощи недавно созданной функции «TextContainsAny» проверьте значения столбца «Ключевая фраза» на наличие слов:
    1. купить
    2. Москва
    3. интернет
32. Загрузите запрос в модель данных.

Эталонное выполнение

Файлы: папка l7_Gold

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб