Alien Sphinx, получилось! Спасибо огромное и за Ваше время и за помощь! !!

Alien Sphinx, спасибо большое! Срабатывает! В колонке Content появилось Table. Но почему-то открывается еще куча всего, хотя папка на sharepoint создавалась только под эти файлы и кроме них там ничего видимого не лежит.

Создаю доп колонку, в ней с помощью функции Text.End вытаскиваю последние 14 символов из названия файла, дальше заменяю ".csv" в конце на пусто и формат меняю на дату.
Думала, что можно просто убрать все значения Error из фильтра, и таким образом убрать эти файлы, но фильтр не открывается на все файлв, и там только даты,  нет значения Error, чтобы их исключить.


И не понимаю, как применит теперь getData ко всем файлам ((( в видике это просто делается, а на своей задаче я не понимаю(

Alien Sphinx, тоже ошибка ((

Alien Sphinx, начала с основного - не получается((((
Источник = SharePoint.Files(<что у вас там сейчас написано>) - там у меня просто ссылка на папку, ее вставляла в кавычках.

Alien Sphinx, либо я неправильно вставила, либо что-то пошло не так.

Последние шаги убрала. В расширенном редакторе строку с формулой вставила в конец. Ругается - "expression.syntaxerror: ожидался токен eof"
Если нажимаю Показать ошибку, то подсвечивает начало формулы "csv_files".


getData код:
(filename)=>
let
   Источник = Csv.Document(File.Contents(filename),[Delimiter=";", Encoding=1251]),
   #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}}),
   #"Повышенные заголовки" = Table.PromoteHeaders(#"Измененный тип", [PromoteAllScalars=true]),
   #"Измененный тип1" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Внешний поставщик", type text}, {"Внешний поставщик.текст", type text}, {"Коммерческий дивизион", type text}, {"Коммерческий дивизион.текст", type text}, {"Товар", type text}, {"Товар.текст", type text}, {"'52.2022", type text}, {"'01.2023", type text}, {"'02.2023", type text}, {"'03.2023", type text}, {"'04.2023", type text}, {"'05.2023", type text}, {"'06.2023", type text}, {"'07.2023", type text}, {"'08.2023", type text}, {"'09.2023", type text}, {"'10.2023", type text}, {"'11.2023", type text}, {"Общий результат", type text}, {"", type text}}),
   #"Строки с примененным фильтром" = Table.SelectRows(#"Измененный тип1", each ([Внешний поставщик] = "103664")),
   #"Удаленные столбцы" = Table.RemoveColumns(#"Строки с примененным фильтром",{"Общий результат"}),
   #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Удаленные столбцы", {"Товар.текст", "Товар", "Коммерческий дивизион.текст", "Коммерческий дивизион", "Внешний поставщик.текст", "Внешний поставщик"}, "Атрибут", "Значение"),
   #"Переименованные столбцы" = Table.RenameColumns(#"Другие столбцы с отмененным свертыванием",{{"Атрибут", "Неделя"}, {"Значение", "Прогноз"}}),
   #"Измененный тип2" = Table.TransformColumnTypes(#"Переименованные столбцы",{{"Прогноз", Int64.Type}}),
   #"Замененное значение" = Table.ReplaceValue(#"Измененный тип2",null,0,Replacer.ReplaceValue,{"Прогноз"})
in
   #"Замененное значение"


Спасибо, что помогаете!

Alien Sphinx, спасибо.

Во вложении, как открылись все csv файлы с sharepoint (закрыла название компании и имя папки только).
Сверху формула в созданном Пользовательском столбце. Значение Error.
getData - это шаблон запроса по одному преобразованному файлу.


Внизу окна (на скрине не видно) сообщение об ошибке:

В запросе "" произошла ошибка. DataFormat.Error: Предоставленный путь к файлу должен быть допустимым абсолютным путем.
Сведения:
  https://(название компании).sharepoint.com/sites/(название папки)/Shared Documents/ZDFOOSM01_Q002_BCAST_1_12.2023_0000103664_2023.01.02.csv

Настоящая ссылка на файл выглядит вот так (недостаточно склеить путь к папке и название файла, как в видео показано):

https://(название копании).sharepoint.com/:x:/r/sites/(название папки)/Shared%20Documents/ZDFOOSM01_Q002_BCAST_1_12.2023_0000103664_2023.01.02.csv?d=w34126760e4f94f1dad771b2bc08e4920&csf=1&web=1&e=s2p0k8

Alien Sphinx, спасибо большое! Отдельно за unpivot и кол-во столбцов в csv файле!!!! И за видос!

Первый пункт получился без проблем.

А вот дальше проблема:
в видео подгружаются все файлики и создается столбец для применения функции по обработке этих файлов, для нее прописывается адрес файла через объединение [Folder Path]&[Name].
У меня файлы лежат на SharePoint. Ссылка на папку короткая - с ней нет проблем, у меня открывается редактор, в котором таблица с названиями этих файлов. Но ссылка на каждый файл - это не folder path + name, ссылка длинная с кучей символов типа $. Как применить формулу с первого шага к куче файлов с SharePoint?

Добрый день!

Прошу помощи с задачей.

Есть 128 файлов в формате csv (1 файл = 1 лист с данными)
В файлах прогнозы продаж. Каждый файл - прогноз от новой даты.
Первые 6 колонок в каждом файле имеют одинаковые названия. Дальше 11-12 колонок с прогнозом (название колонки - это номер недели). С появлением новых файлов номера недель сдвигаются=меняются. Последняя колонка Итог - название одинаковое во всех файлах.
Вторая строка ненужная)) С третьей строки начинаются данные. Количество строк в файлах отличается.

Нужно собрать информацию по прогнозам из всех файлов в один (файлы будут продолжать накапливаться).
Но сначала в каждом файле нужно сделать первую строку заголовком, вторую удалить и последний столбец удалить.
Преобразованные файлы объединить (с добавлением, чтобы были столбцы с номерами недель из всех файлов) + добавить столбец с датой файла (она в одинаковом формате (_2023.04.07) есть в названии каждого файла).

Excel 2016

Примеры файлов во вложении.

Пытаюсь сделать через Power Query. Все файлы загружаю в один запрос, но не понимаю, как выполнить эти однотипные преобразования сразу для всех файлов, чтобы потом их объединить. Или может изначально не надо все загружать в запрос?

Заранее спасибо.

Андрей Лящук,

Исходные данные из разных отчетов, в одном может чего-то не хватать, что есть в другом, но финально должно быть объединение.
Подытоги должны быть по месяцу и категории, как в этих отчетах.
Просто для подитога строка пустая остается.


Я обычный пользователь Excel и только начинаю изучать PQ. Я знаю, что с его помощью можно сделать объединение, но я не знаю еще алгоритмов.
Поэтому прошу помощи во всем.
Куда я должна вставить формулу? Что я должна предварительно сделать?
Я очень ценю помощь, но сейчас она написана для уже опытного пользователя.

Я сейчас делаю подключение к книге. Каждый лист редактирую в подключении - правлю формат даты, убираю слово Итог, оставляю только столбики с датой, категорие, кодом и метриками.
Потом делаю объединение со справочником, чтобы подтянуть описание к кодам.
Сейчас поняла, что у меня еще и названия категорий в отчетах не везде совпадают. Сделала отдельный справочник на категории, создала к нему подключение. Объединила подключения. Но что-то пошло не так и цифры затроило, когда категорию подсоединила. И это не прям легкий и красивый в целом вариант.
наверно, можно сделать проще. И прошу объяснить это простым языком.

Буду очень-очень благодарна.

Михаил, здравствуйте!

Спасибо огромное, постараюсь.
А можете алгоритм словами написать, как применять.
Я только начинаю работать с PQ...

Здравствуйте, Андрей!

Обновила файл с примером - добавила вкладку Результат. Извините, что не сразу.

Сложность еще, что на 3х "рабочих" листах данные не полные, т.е. где-то есть январь, а где-то нету, или sku отсутствуют, но нужно объединить все имеющиеся данные, и обязательно сохранить промежуточные итоги по subcat и дате.

Спасибо за помощь.

Здравствуйте!

Помогите, пожалуйста, с преобразованием и объединением таблиц воедино для формирования отчета.
Вводные:
в книге 5 листов.
Лист SO и Лист FA: категория товара, OSKU код товара, описание sku, дата и метрики (на каждом листе свои)
Лист CFR: категория товара, артикул товара, описание sku, дата, метрики
Есть лист справочник с 2мя столбцами артикул и OSKU
Есть лист справочник с OSKU и несколько столбцов с описанием sku (на листах разное везде)

Строка уникальна на уровне даты и кода товара (OSKU или артикул)

Сложность: есть подитоги для категории и для каждого месяца, и их нужно сохранить.

Как правильно преобразовать и объединить листы? Не понимаю, не получается с подитогами :-((

Нужно, чтобы на выходе была информация вида:
Категория-Дата-OSKU-Столбцы с описанием - метрики со всех 3х листов
С подитогами для категории и каждого месяца

Использую Office365 ProPlus.
Пример во вложении.

Заранее огромное спасибо!

я не знаю, как изменить название, вижу только замену в сообщении.
И по самому названию - мне нужно заменить суммпроизв() на более быструю формулу (если возможно)

vikttur,


она считает 1 ячейку с условиями минут по 15, а таких ячеек много.
Мне бы хотелось заменить функцию на более быструю, но я не знаю на какую

Добрый день!

Пожалуйста, подскажите, можно ли какой-то более быстрой формулой заменить суммпроизв(), если нужно:
посчитать сумму уникальных чисел по 2-м условиям.
Диапазон около 400 тыс строк ...
Сейчас считаю по формуле: =СУММПРОИЗВ((диапазон1=значение1)*(диапазон2=значение2)*--(ПОИСКПОЗ(диапазон3;--диапазон3;)=СТРОКА(диапазон3)-1);диапазон3)
все висит ...(((
Excel 2016

Максим Зеленский,у нас только появился Power BI, еще не освоили.
Буду очень-очень благодарна, если напишите подробно, как сделать график в нем.

Спасибо заранее.

Sceptic,пример во вложении.

Добрый день!

Есть продажи за период Q2 и Q4 и 3 основных фактора влияния.
По этим данным строю водопад - проблем нет.
Но нужно, в идеале, сделать так, чтобы наиболее объемный фактор (падения в данном случае) "раскладывался" как бы вторым уровнем водопада на 9 территорий, чтобы сразу было видно в чем и где косяк, на больших цифрах.
Подскажите, пожалуйста, так можно на одном графике сделать? Как?
Пользуюсь офисом 2016г.

Спасибо заранее.

Добрый день!

Недавно совсем узнала о функциях КУБ(), пока разбираюсь, но прилетел новый отчет, который нужно было сделать вчера))

и я точно знаю, что можно упростить себе жизнь с помощью этих функций, но прописать не получается.

Вводные:
Есть куб OLAP со всевозможными данными о продажах.
Мне нужно создать таблицу:
1 столбец - бренды - первая 10-ка по отклонению плана продаж от факта
на каждый бренд должно быть отведено 3 строки, чтобы:
2 столбец - дивизион - подтянуть к каждому бренду наиболее отстающие 3 дивизиона по нему
3 столбец - отклонение по абсолюту
4 столбец - отклонение по %
и т.д.

В кубе (куб сам не могу подгрузить) есть Ассортимент (там есть группа Брендов), есть Период (там есть отдельно Год, отдельно Месяц), есть География (там есть Дивизион, Регион и т.д.) и в метриках есть План продаж, Факт продаж, % отклонения плана от факта.

Месяц и год я задаю вверху отчета в ячейках выбором из списка.



Не могу найти подробное описание с примерами по этой функции. Помогите, пожалуйста.

Заранее спасибо.

Спасибо большое!

Все протянулось.

Добрый день!

Подскажите, пожалуйста, есть ли быстрый способ ввести формулу массива на большое число строк.
Есть много строк (больше 200) с данными по месяцам, для каждой строки нужно посчитать a и b для линейного тренда.
Как "протянуть" формулу для всех строк, чтобы не вводить массив для каждой строки?

Заранее спасибо.

Волшебство!

СПАСИБО ОГРОМНОЕ! :-)

Ок, а если не ВПР, то как? Помогите с формулой, пожалуйста.

Спасибо.

Добрый день!

Есть таблица с показателем отклонения по бренду по территориям.
Нужно в другой таблице к заданным выборочным брендам "притянуть" 3 территории с наибольшим показателем отклонения (топ3), причем не важно отклонение в + или в - для заданного периода.

Подскажите, пожалуйста, как сделать?
Заранее спасибо.

Ігор Гончаренко,спасибо, но ссылка в формате R1C1, не понятно, куда ссылается.

Добрый день!

Помогите, пожалуйста, с формулой.
В отчете нужно прописать отклонение от предыдущего периода (некоторые периоды пропущены), а в конце формула должна считать отклонение последнего периода от первого.
С макросами не работаю( нужно просто формулой прописать.

Мини пример во вложении.
Заранее большое спасибо!

Доброго дня!

Есть умная таблица со срезами.
Мне нужно по текстовому столбцу посчитать количество уникальных значений.
Формула должна считать уникальные значения только по видимым (отфильтрованным срезом) ячейкам.
Данные в столбце только текстовые и без пустых ячеек.
Excel 2016.

Во вложении маленький пример. Реальная таблица порядка 3 тысяч строк и 20 колонок.

Заранее спасибо.