Power Query Базовый №3. Импорт csv, txt, таблиц, диапазонов
HTML-код
- Опубликовано: 17 окт 2024
- В этом уроке мы научимся импортировать данные из файлов TXT, CSV. Также мы научимся подключаться к таблицам и именным диапазонам Excel.
Файлы для спонсоров Boosty: boosty.to/comr...
Файлы для спонсоров ВК: comrade...
Файлы для спонсоров RUclips: • Post
Страница урока на сайте: comrade-xl.ru/...
Файлы для спонсоров Boosty: boosty.to/comrade-xl/posts/155889a5-867b-477a-ad91-6d4ea8ce026b?share=post_link
Файлы для спонсоров ВК: vk.com/comrade.excel?w=wall-185123800_57
Файлы для спонсоров RUclips: ruclips.net/user/postUgyplDz7TKF_4q8503F4AaABCQ
Страница урока на сайте: comrade-xl.ru/2020/03/09/power-query-import-tablic-diapazonov-csv-txt/
Спасибо за курс ! Хотелось бы, чтобы по ходу видео немного объясняли про разные типы файлов с данными, в чем разница и для чего применяются
Примерно с 10-й минуты (с создания динамического диапазона) не понимаю, что происходит и для чего это? Можно, конечно, изловчиться и создать диапазон с помощью функции СМЕЩ, но зачем? Есть же волшебное сочетание CTRL + T! Сделал таблицу умной и не паришься
Добрый вечер!
Не получилось добавить динамический диапазон. Не могу понять почему.
Формула в приложенном Вами файле - другая. А хочется использовать ту, что приводится в уроке. Может, я неверно ее ввожу при присвоении имени? =СМЕЩ(ДинамическийДиапазон!$A$1;0;0;СЧЁТЗ(ДинамическийДиапазон!$A:$A);СЧЁТЗ(ДинамическийДиапазон!$1:$1))?
Не появляется таблица при формировании пустого запроса (
У Вас верная формула. Сравнила Вашу и свою - различий нет
У меня все получилось )
Подскажите, пожалуйста, как это использовать в реальной практике? Данные в PQ же автоматически обновляются при внесении изменений в диапазон, зачем все это надо?
Тимур, прежде всего поблагодарить за проделанную работу хочу, курс классный. Не нашёл сходу ответа на вопрос свой. Если я делаю импорт данных из json или xml, то позиции в виде объектов преобразуются в виде структурированного столбца. Разворачиваю я их путем нажатия на стрелки на каждый столбец. Можно разом развернуть все свёрнутые? Когда с зажатым ctrl два и более структурированных выбираю, кнопка "развернуть" прозрачной становится.
Спасибо за уроки!
Не понятно с загрузкой таблицы в виде диапазона. Кучу вопросов, куда, зачем, откуда. Хотелось бы чтоб подробнее переходы были в объяснениях
Спасибо, у вас отличные уроки!
Спасибо!
Спасибо! Хорошее видео.
Для себе открыл где можно использовать функции СМЕЩ(OFFSET) и СЧЕТЗ(COUNTA)...
В особенности функцию СЧЕТЗ - не знал((.... Ранее при определении последней строки/количество строк/столбцов... использовал VBA... Тепер вижу есть хорошие и более простые методы...
Благодарю за ваш труд!
Очень полезно , спасибо !
Подскажите, почему может не появляться ДинамическийДиапазон после добавления формулы в запросах, у меня отображается только Таблица и Диапазон?
Это может быть, если формула при помощи который создан именной динамический диапазон введена с ошибками. Перепроверьте. Скачайте мои файлы, где задача уже решена и сравните мою формулу с вашей.
@@comrade_excel В видео вы показываете создание динамического диапазона через функцию СМЕЩ, а в готовом файле динамический диапазон у вас реализован через ИНДЕКС и ПОИСКПОЗ. Так что сравнить формулу не получится.
Динамический диапазон:
сделал через умную (сводную) таблицу и не стал париться с формулами.
PQ сам обновляет данные (т.е. не надо нажимать "Обновить предварительный просмотр")
Да и в сам PQ заходить не надо т.к. есть подключение
В Excel только останется : "Данные--Обновить все" и данные автоматически оявляются
У кого ошибка с тем, что в пустом запросе не появляется динамический диапазон - проверьте формулу для высоты и ширины диапазона. Я вместо СЧЁТЗ написал СЧЁТЗНАЧ, поэтому не появлялось.
Спасибо! Круто
Очень нужна Ваша помощь! Ежедневно собираю csv файлы из папки. Все было хорошо, но некоторое время назад данные из новых файлов в запрос выгружаются криво, то есть смещаются столбцы с данными. При этом количество столбцов из файлов все то же самое, наполнение столбцов такое же(
Криво выгружаются отдельные строки или целые файлы?
Спасибо автору за курс. Желаю удачи во всех начинаниях.
П.с. надеюсь придёт момент, когда и жители Украины смогут поддержать автора спонсорской подпиской.
Спасибо! Можете попробовать сделать как в этом видео ruclips.net/video/oFZ3NxIyKxM/видео.html
Добрый день. У меня при запросе таблиц через CurrentWoorbook вообще не видит динамический диапазон ( ни в вашем готовом решении, ни в моем сделанном по видео)
Добрый день!
Проверьте следующее:
1. Совпадает ли язык Excel у вас с моим из видео.
2. Правильно ли вы ввели формулу.
3. Если вы уверены, что формулу вы ввели верно, то попробуйте другую формулу динамического именного диапазона.
Саморасширяющийся именной диапазон можно задать с помощью СМЕЩ или с помощью ИНДЕКС. Попробуйте иной вариант.
У Вас в вопросе есть опечатка: "CurrentWoorbook", а должно быть "CurrentWorkbook". Возможно, что такая же ошибка в формуле.
@@comrade_excel У меня такая же проблема, в Вашем готовом выходит ошибка
Expression.Error: Ключу не соответствует ни одна строка в таблице.
@@МарияПоничева Здравствуйте! Пишите, пожалуйста, на Boosty или в ВК. Опишите к какому именно файлу вы подключаетесь и приложите скриншот с ошибкой.
Спасибо за урок! Почему может не появляться динамический диапазон, если формула идентична вашей? Я даже скопировала ее у вас
Если в моем файле формула работает, а в вашем нет, то значит у вас что-то не так. Адаптируйте формулу под ваши данные.
Подскажите, при создании запроса к TXT файлу с однотипными строками (330 тыс) несколько строк вставляет в одну ячейку таблицы:
8642 06080230204 F3 РЕЗЬБОВАЯ ЗАГЛУШКА 2.51
8643 06080230207 F3 РЕЗЬБОВАЯ ПРОБКА 5.16
8644 06080230208 F3 DRAIN PLUG M24X1,5-ST-ZNPHR3F 2.65
8645 06080230222 F3 DRAIN PLUG M48X1,5-ZNPHR3F 86.16
8646 06080230605 F3 DRAIN PLUG M18X1,5-5.8-A3C 3.12
8647 06080230630 F3 DRAIN PLUG M8X1-ST-A3C 1.26
8648 06080230641 F3 DRAIN PLUG R1/8"-ST-A3C 1.53
8649 06080230730 F3 DRAIN PLUG M8X1-ST-MAN183-B1 2.40
8650 06080290000 F3 DRAIN PLUG 3.65
8651 06080290002 F3 DRAIN PLUG 3.65
8652 06080290003 F3 DRAIN PLUG 5.05
8653 06080290004 F3 DRAIN PLUG 6.28
начиная с 8648 строки следующие 70 строк идут как одна ячейка.
После чего снова идет нормально разделение строк.
И так несколько раз.
Здесь дело не в Power Query, а в самом файле источнике и в системе, из которой вы выгружали данные. В этих 70 строках нарушен порядок расположения табов, поэтому они не читаются нормальным образом.
1. Лучше всего, конечно, попытаться сначала выгрузить данные по-другому, например, вместо TXT использовать CSV. Часто система позволяет выбрать формат и разделитель. Поэкспериментируйте с выбором и вопрос решится сам собой.
2. Если первым пунктом ничего не получается, то в Power Query нужно выполнить сегментацию данных. Нужно весь датафрейм разделить на 2 части. Тот, что читается правильно отделить от того, что читается неправильно. Тот, что читается неправильно нужно обработать иначе, а потом обратно эти 2 датафрейма объединить в 1.
Загрузите файл в облако и в ответном комментарии укажите ссылку. Мне интересно посмотреть на этот файл. Возможно, из этого получится хороший урок на тему сегментации.
@@comrade_excel drive.google.com/file/d/1pEeZ0X8ejjHEn3NvmgAQ3U1-CILvjPJ6/view?usp=sharing
@@comrade_excel нет. пробовал и по количеству знаков в строке и по табам
@UCwAru3vDmH-IdYtwK7JWN4w 1с это замечательно делает через количество знаков в строке. При создании запроса вроде все тоже идет хорошо но только до 8648 строки. Я не могу разобраться что в фале лишнее или чего не хватает чтобы разорвать строку.
Из этого файла как раз получится хороший урок. Вот код решения. В шаг Источник вставьте путь к файлу на вашем ПК.
let
Источник = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\timur.kryukov\Downloads\comrade.excel ideas\Прислано\01 2020 effective from 060120.txt"), null, null, 1251)}),
#"Удаленные верхние строки" = Table.Skip(Источник,1),
#"Разделить столбец по положению" = Table.SplitColumn(#"Удаленные верхние строки", "Column1", Splitter.SplitTextByPositions({0, 21}, true), {"Column1.1", "Цена"}),
#"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по положению",{{"Column1.1", type text}, {"Цена", type text}}),
#"Разделить столбец по разделителю" = Table.SplitColumn(#"Измененный тип", "Column1.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Порядковый номер", "Column1.1.2"}),
#"Обрезанный текст" = Table.TransformColumns(#"Разделить столбец по разделителю",{{"Column1.1.2", Text.Trim, type text}}),
#"Разделить столбец по разделителю1" = Table.SplitColumn(#"Обрезанный текст", "Column1.1.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Артикул", "Column1.1.2.2"}),
#"Разделить столбец по разделителю2" = Table.SplitColumn(#"Разделить столбец по разделителю1", "Column1.1.2.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Код марки", "Название"}),
#"Обрезанный текст1" = Table.TransformColumns(#"Разделить столбец по разделителю2",{{"Название", Text.Trim, type text}}),
#"Измененный тип с языком" = Table.TransformColumnTypes(#"Обрезанный текст1", {{"Цена", type number}}, "en-001"),
#"Измененный тип1" = Table.TransformColumnTypes(#"Измененный тип с языком",{{"Порядковый номер", Int64.Type}})
in
#"Измененный тип1"
Я тут был 11.10.2022
ну надо же! а я тут 11.10.2023))))