Страницы: 1
RSS
Разбивка столбцов исходной таблицы по разделителю, их последующая привязка к конкретной таблице-шапке по общим именам, Разделить текст не в одном столбце, а сразу в нескольких
 
Добрый день.
Есть таблица такого вида:
Скрытый текст

Мишка, Зайка, Бегемот - это названия столбцов.
Нужно в POWER QUERY разбить текст в столбцах по разделителю ":", но так, чтобы не для каждого столбца делать это в отдельности, а выделив сразу все три столбца.
В выше указанной таблице количество столбцов может меняться в большую и меньшую сторону и соответственно иметь новые заголовки. И каждый раз при обновлении данных приходится редактировать запрос в Power Query под конкретные заголовки столбцов.
Столбцы должны располагаться именно в таком виде, как в образце, т.к. они потом выгружаются на лист под конкретную шапку таблицы.
Заранее благодарю за советы.
 
Доброе время суток
Цитата
turbidgirl написал:
а выделив сразу все три столбца
Это нет. Написав соответствующий код для
Цитата
turbidgirl написал:
количество столбцов может меняться в большую и меньшую сторону и соответственно иметь новые заголовки.
можно.
 
Благодарю за ответ. В общем то, я так и предполагала. Решила проблему тем, что создала два шага в Power Query cо значениями в столбцах до ":" и после ":", а потом просто объединила эти две таблицы в одну. Связку сделала по Индексу строки.
 
Цитата
turbidgirl написал:
а потом просто объединила эти две таблицы в одну.
А можно поинтересоваться - откуда взялась вторая таблица? Я бы, честно говоря, так делал - вполне себе мышиный хардкод, не зависящий от числа и названий столбцов при такой структуре данных, как в примере.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Товары"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Индекс", 0, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Индекс"}, "Товар", "Значение"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Значение", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Сумма", "Основание"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Индекс"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Сумма", type number}, {"Основание", type text}, {"Товар", type text}})
in
    #"Changed Type"
 
На выходе мне нужно было получить таблицу, что представлена в прикрепленном файле.
Эти данные потом подставляются под определенную шапку в другой таблице.  
Получила этот конечный результат следующим образом:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Добавлен индекс" = Table.AddIndexColumn(Источник, "Индекс", 0, 1),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Добавлен индекс", {"Индекс"}, "Атрибут", "Значение"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Другие столбцы с отмененным свертыванием", "Значение", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Значение.1", "Значение.2"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Значение.1", type number}, {"Значение.2", type text}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"Значение.2"}),
    #"Сведенный столбец" = Table.Pivot(#"Удаленные столбцы", List.Distinct(#"Удаленные столбцы"[Атрибут]), "Атрибут", "Значение.1"),
    Пользовательская1 = #"Измененный тип",
    #"Удаленные столбцы1" = Table.RemoveColumns(Пользовательская1,{"Значение.1"}),
    #"Сведенный столбец1" = Table.Pivot(#"Удаленные столбцы1", List.Distinct(#"Удаленные столбцы1"[Атрибут]), "Атрибут", "Значение.2"),
    #"Объединенные запросы" = Table.NestedJoin(#"Сведенный столбец",{"Индекс"},#"Сведенный столбец1",{"Индекс"},"Сведенный столбец1",JoinKind.LeftOuter),
    #"Развернутый элемент Сведенный столбец1" = Table.ExpandTableColumn(#"Объединенные запросы", "Сведенный столбец1", {"Индекс", "Мишка", "Зайка", "Бегемот"}, {"Индекс.1", "Мишка.1", "Зайка.1", "Бегемот.1"}),
    #"Удаленные столбцы2" = Table.RemoveColumns(#"Развернутый элемент Сведенный столбец1",{"Индекс", "Индекс.1"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы2",{"Мишка", "Мишка.1", "Зайка", "Зайка.1", "Бегемот", "Бегемот.1"})
in
    #"Переупорядоченные столбцы"
Изменено: turbidgirl - 23.09.2018 13:54:21
 
У вас шаги #"Развернутый элемент Сведенный столбец1", #"Переупорядоченные столбцы" используют статические имена столбцов. Как только названия и/или число столбцов поменяется, то придётся их переделывать. Вы можете выложить пример с несколькими исходными таблицами (разными именами столбцов и их количеством) и как должен выглядеть конечный результат трансформации?
 
По факту, у меня есть пять компаний. Шапка, под которую подгоняются данные выглядит так, как в приложенном файле. В шаге #"Развернутый элемент Сведенный столбец1" перечислила все пять компаний, а вот шаг #"Переупорядоченные столбцы" вообще убрала. Потом путем подстановки одного запроса под другой сливаю данные. Пример файла прилагаю.  
Изменено: turbidgirl - 23.09.2018 11:18:40
 
Вариант с динамическим связыванием столбцов исходной таблицы и таблицы-шапки по общим именам столбцов. Было лениво, не сделал предварительную перед разбиением фильтрацию столбцов исходной таблицы. По идее, теме не мешало бы дать правильное название, хотя бы
Power Query. Разбивка столбцов исходной таблицы по разделителю и их позиционирование под таблицей-шапкой по общим именам.
 
Андрей, спасибо! Изящное решение! Буду разбираться. Особенно впечатлил блок, где были разделены данные во всех столбцах одним махом. Мои знания в языке М не такие продвинутые. А вот тему я поменяла бы, но вот только не разобралась как именно это сделать...
 
Предложите новое название в отдельном сообщении - модераторы подменяют.
Вот горшок пустой, он предмет простой...
 
функция PQ - List.Zip - не распознаётся почему-то в 2016 ?..
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
PQ - List.Zip - не распознаётся почему-то в 2016
Доброе утро, JeyCi. Правильно ли я понимаю, что вы последний раз обновляли Excel в 2016? Функцию добавили в 2017. Практически каждый квартал появляется что-то новое.
 
ну вобщем-то да  :oops: - Спасибо за разъяснения!  :)  
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
вас не затруднит выложить просто код? -
сбой на typeList = List.Zip({nameList, {Number.Type, Text.Type}}),
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
вас не затруднит выложить просто код?
А в чем проблема посмотреть его в расширенном редакторе?
Вот горшок пустой, он предмет простой...
 
JeyCi, может проще обновления установить?
 
Цитата
Андрей VG написал: JeyCi , может проще обновления установить?
сейчас не та скорость нета... а к стационар пк сеть пока не подключена... вот и спросила: чтобы увидеть новшества... имхо... а в чём проблема опубликовать код? (если под рукой).. прямо как допрос  :( ... - бывают же обстоятельства...  
Изменено: JeyCi - 09.10.2018 12:42:49
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
в чём проблема опубликовать код?
Да не проблема. Просто, как и коллега PooHkrd, полагаю, что этот код не проблема посмотреть в расширенном редакторе Power Query. Или у вас и к нему нет доступа?
Скрытый текст

Успехов.
 
вот я его таким и увидела  :oops: ... подумала, что List.Zip прячет какую-то функцию и заменяет на слово Zip (т.к. из архива файл)... sorry... спасибо! - значит это всё-таки функция...вопрос снят... (интересное её [этой функции] применение в данном контексте)
Изменено: JeyCi - 09.10.2018 12:53:45
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал:
интересное её [этой функции] применение в данном контексте)
Соединить список названий столбцов со списком типов данных, чтобы получить список для Table.TransformColumnTypes
 
ok, буду знакомиться... thanks ещё раз!
Изменено: JeyCi - 09.10.2018 13:07:25
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Страницы: 1
Наверх