Как удалить параметры из URL страниц? Массовый способ в Power Query

В отчетах по посещаемости страниц сайта иногда есть проблема: список искусственно «раздут» из-за наличия GET-параметров в URL-адресах. Если это типовые регулярные отчеты из одного источника, то можно не удалять параметры каждый раз вручную, а настроить автообработку файла с помощью Power Query и исправлять новые отчеты в пару кликов.

Для примера буду использовать демо-счетчик Яндекс.Метрики https://metrika.yandex.ru/dashboard?id=44147844 . В его отчете по страницам входа встречаются URL с динамическими параметрами и якорными ссылками, что создает дубли в списке.

Отчет из интерфейса Яндекс.Метрики нужно экспортировать в файл .xlsx:

Скаченную таблицу – переименовать в «Исходник Отчет по страницам».

Затем, создать новый пустой файл Excel и назвать его «Обработчик Отчет по страницам».

После переименования файлов, зайти в «Обработчик Отчет по страницам», чтобы выполнить подключение к «Исходник Отчет по страницам». Путь: Данные – Получить данные – Из файла – Из книги:

В качестве источника нужно указать файл «Исходник Отчет по страницам» и нажать кнопку «Импорт».

Появится окно-навигатор – в нем кликнуть на вкладку «Отчет» и кнопку «Преобразовать данные»:

Откроется интерфейс Power Query:

В Power Query первым делом нужно превратить сырую выгрузку Яндекс.Метрики в рабочий массив данных. В данном случае – убрать шапку таблицы и оставить только два столбца, «Страница входа» и «Визиты».

В Column2 – убрать все пустые строки (null) с помощью фильтра :

В Column1 – скрыть строку «Итого и средние» с помощью фильтра :

Затем, выделить первые два столбца и выбрать действие «Удалить другие столбцы»:

В конце – нажать на верхней панели кнопку «Использовать первую строку в качестве заголовка»:

Теперь имеется готовый к обработке массив данных:

Удалить параметры – т.е. удалить всё, что указано в URL после вопросительного знака.

Нужно выделить первый столбец и кликнуть на Главной «Разделить столбец – По разделителю». Выбрать «Пользовательский», ввести «?», указать «Самый левый разделитель» и «ОК»:

В результате все параметры переместятся в отдельные столбцы:

Удалить якорные ссылки – т.е. удалить всё, что указано в URL после знака решетки.

Нужно выделить первый столбец и кликнуть на Главной «Разделить столбец – По разделителю». Выбрать «Пользовательский», ввести «#», указать «Самый левый разделитель» и «ОК»:

В результате, все якори переместятся в доп. столбики, а в первом столбце останутся очищенные URL-адреса:

На данном этапе образовалось много лишних столбцов, поэтому нужно выделить первый+последний столбцы, и выполнить действие «Удалить другие столбцы»:

Видно, что столбец «Визиты» имеет текстовый формат (abc). Это в дальнейшем станет препятствием для проведения математических операций. Поэтому для столбца «Визиты» нужно поменять формат данных на числовой (123). А первому столбцу можно вернуть исходное название «Страница входа» вместо «Страница входа 1.1.1»:

В зависимости от конкретных исходников и целей их обработки, может понадобиться удалить строки с поддоменами, склеить URL c www и без www, привести все символы к нижнему регистру… Все эти действия – как раз на данном этапе.

В нашем случае все хорошо, кроме того, что в списке есть не только https-протокол, но и http. Можно подставить во все URL защищенный протокол с помощью кнопки «Главная – Замена значений»:

Таким образом, все строки таблицы приведены к единому виду:

Однако, все еще нет полной картины по визитам на конкретные URL, т.к. они дублируются в списке.

В Excel понадобилась бы формула =СУММЕСЛИ, а в Power Query ее аналогом является готовая опция в интерфейсе – «Группировать по».

Нужно выделить первый столбец, нажать «Группировать по» и задать следующие настройки:

· группировка – Страница входа;

· имя нового столбца, в котором будут суммированы визиты – Визиты всего;

· тип операции – Сумма;

· столбец для выполнения операции – Визиты.

После нажатия на ОК, все дублирующиеся URL будут склеены, а визиты по ним – объединены в одно значение.

После успешной группировки можно выходить из Power Query – по кнопке «Закрыть и загрузить»:

Сокращенная таблица отобразится в файле «Обработчик Отчет по страницам»:

Алгоритм обработки Power Query уже настроен, и теперь его можно использовать для всех аналогичных выгрузок.

Потребуется только перезалить источник данных. Например, можно удалить старый файл «Исходник Отчет по страницам», и загрузить в ту же папку новый актуальный файл с таким же названием.

Затем, открыть «Обработчик Отчет по страницам» и нажать кнопку «Обновить все» на вкладке Данные:

За несколько секунд Power Query полностью повторит все записанные шаги на свежих данных и подгрузит обновленный отчет на вкладку Excel в файле «Обработчик Отчет по страницам».

В шагах 1-5 был обработан отчет за январь-июнь 2025. Теперь можно сделать выгрузку того же отчета, но сразу за весь 2024 год.

Экспортированный файл выглядит следующим образом:

Необходимо удалить старый файл «Исходник Отчет по страницам» из папки, и добавить новый файл с данными за 2024 год под тем же именем «Исходник Отчет по страницам». А в файле «Обработчик Отчет по страницам» – запустить обновление на вкладке Данные.

Результат – обновился итоговый отчет в файле «Обработчик Отчет по страницам». Теперь вместо 8 строк за январь-июнь 2025, появились 45 строк за 2024 год:

Можно подключаться не к одному файлу, а сразу к целой папке. Например, в качестве источника для рабочих SEO-отчетов я создаю папки с ежемесячными выгрузками, где каждый файл имеет имя «site.ru Страницы входа 2025 06», «site.ru Страницы входа 2025 07» и т.д. При подгрузке в Power Query названия файлов записываются в отдельный столбец, и каждый месяц массив дополняется без перезалива файла-источника, а просто добавлением нового файла в папку.

__

Power Query – это про то, чтобы один раз задать шаги по обработке данных, и использовать их неограниченное количество раз на типовых исходниках. Если задача по склеиванию URL с параметрами является разовой, то проще сделать это в Excel.

__

На шаге 4 есть смысл подтянуть еще одну таблицу в качестве источника – выгрузку из Screaming Frog, где все URL проверены на код ответа сервера. Тогда можно подготовить отчет только по актуальным страницам сайта, или же наоборот, только по битым.

__

Алгоритм обработки отчета можно использовать не только на разных периодах по одному проекту, но и сразу для нескольких сайтов, т.к. конкретный домен не имеет значения.

Главное – ничего не менять в названии файла и вкладки (т.к. это указание на источник) и в самой структуре файла (т.к. это ломает записанный алгоритм действий). Один шаг влево-вправо, и Power Query выдаст ошибку.

Обсудить статью и найти больше материалов: