Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Возможно ли быстрое перемещение столбцов в Power Query?
 
#1
15.06.2019 19:40:03
Подскажите, пожалуйста, есть ли способ расставить столбцы в Power Query в определённом порядке?
Имеется 50 столбцов, перетаскивать по одному очень долго.  
 
 
 
#2
15.06.2019 20:03:12
Цитата
Maayun написал:
в определённом порядке
по какой логике порядок?
 
 
 
#3
15.06.2019 20:16:01
artyrH, названия столбцов в исходных файлах:
1. Номер клиента Имя клиента Адрес клиента Улица 1 Улица 2 Город Область Индекс Широта Долгота
2. Клиент номер KLABC Название ИНН КПП Плательщик Район Плательщик Дом дополнение Условие платежа Клиент Город Клиент Улица Клиент Дом Клиент Район

Требуется:
Номер клиента ABC-класс Имя клиента Сегмент ИНН КПП DEAD (метка удалён) Пл-щик Юридический Адрес

Как видите, идут через одну, а таких столбцов 50 штук.
Изменено: Maayun - 15.06.2019 20:29:38
 
 
 
#4
15.06.2019 20:35:26
Цитата
Maayun написал:
DEAD (метка   удалён)Пл-щикКак видите, идут через одну
лучше в файле таблицу столбцов двадцать как есть и как нужно расставить
 
 
 
#5
15.06.2019 20:41:23
Доброе время суток.
Цитата
artyrH написал:
по какой логике порядок?
А принципиально?
Просто нужен список названий столбцов в правильном порядке и использовать Table.SelectColumns(sourceTable, СписокИмёнСтолбцовВПравильномПорядке) :)
 
 
 
#6
15.06.2019 21:11:22
Цитата
Андрей VG написал:
А принципиально?
да нет. если известны имена столбцов и их нужное расположение, то не принципиально
 
 
 
#7
15.06.2019 21:37:43
Андрей VG, попыталась поменять расположение названий в коде, выскакивает ошибка "ожидался токен Comma".
Подскажите, пожалуйста, вот код того, что я объединила два файла, обозначила столбцы из второго файла. Что прописать до слова Table и как определить sourceTable из двух?)
Код
let
    Источник = Table.NestedJoin(#"rout out",{"Номер клиента"},cst,{"Клиент номер"},"cst",JoinKind.LeftOuter),
    #"Другие удаленные столбцы" = Table.SelectColumns(Источник,{"Номер клиента", "Имя клиента", "Область", "Индекс", "Широта", "Долгота", "Способ прод", "cst"}),
    #"Развернутый элемент cst" = Table.ExpandTableColumn(#"Другие удаленные столбцы", "cst", {"KLABC", "Название", "ИНН", "КПП"})
    ??? = Table.SelectColumns(sourceTable, СписокИмёнСтолбцовВПравильномПорядке)
in
    #"Развернутый элемент cst"
Изменено: Maayun - 15.06.2019 21:42:25
 
 
 
#8
15.06.2019 22:41:18
.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    a = Table.FromList(Table.ColumnNames(Source), Splitter.SplitByNothing()),
    b = List.Reverse(Table.AlternateRows(a,0,1,1)[Column1]),
    Custom1 = Table.SelectColumns(Source,b)
in
    Custom1
 
 
 
#9
16.06.2019 06:08:35
artyrH, я предполагал более простой подход. Имеем в книге таблицу, задающую правильный порядок вывода столбцов (в примере на листе "Правило порядка". Её столбец с именами и используем.
 
 
 
#10
16.06.2019 07:30:32
Андрей VG, здравствуйте
вообще, я ждал пример от тс. примера не получили, только прозвучало что идут через одну. я и сделал что идут через одну, но с конца)
это тот случай когда принципиальна какая то логика без известных имен столбцов
 
 
 
#11
16.06.2019 10:24:54
Цитата
artyrH написал:
вообще, я ждал пример от тс. примера не получили
Это, да, соглашусь, при невнятности объяснения (ТС в #3 постеснялся даже ; расставить, чтобы отделить одно имя поля от другого) можно построить множество равновеликих гипотез, что собственно является решением с его точки знения.
Боюсь только, примера не дождёмся. ТС с утра был, но ни тебе спасибо, ни что не так, не говоря о всё том же примере :(
 
 
 
#12
16.06.2019 10:35:47
тема еще найдет кому быть полезной 8)  
 
 
 
#13
16.06.2019 18:06:52
Андрей VG, с телефона не видел ваш вариант для случая имеющегося шаблона с правильным порядком столбцов, но из своего опыта обучения мышкоклацателей самый доходчивый вариант это Table.Combine, где первый элемент списка это пустая таблица с шаблоном заголовков. Он же самый быстрый при большом количестве таблиц.
Изменено: PooHkrd - 16.06.2019 18:08:02
Вот горшок пустой, он предмет простой...
 
 
 
#14
16.06.2019 18:57:07
Цитата
PooHkrd написал:
первый элемент списка это пустая таблица с шаблоном заголовков
попробовал сделать. получилось так
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    a= #table(List.Alternate(List.Reverse(Table.Schema(Source)[Name]),1,1,1),{} ),
    b= a&Source,
    c= Table.SelectColumns(b,List.Alternate(List.Reverse(Table.Schema(Source)[Name]),1,1,1))
in
    c
 
 
 
#15
16.06.2019 19:14:12
Самый мышкоклацательный подход: зажимаем Ctrl и прокликиваем заголовки ВСЕХ столбцов в нужном порядке. Затем выполняем команду с ленты "Удалить другие столбцы".
Но при большом количестве столбцов, конечно, муторно :)
F1 творит чудеса
 
 
 
#16
16.06.2019 21:00:32
artyrH, Андрей VG, добрый вечер.
Прошу прощения, пыталась самостоятельно применить способ, который предложил Андрей, но не получается: всё та же ошибка "Ожидается токен Comma" :( .
Прикладываю файлы и пояснения: порядок столбцов обусловлен порядком столбцов в файле База.xlsx (8.93 КБ). Еженедельно файлы rout out.xlsx (9.03 КБ) и cst.xlsx (8.82 КБ) обновляются и я хотела с помощью PQ автоматически консолидировать в файле База, обновляя исходные файлы.
Андрей VG, можно ли в запросе задать порядок столбцов исходя из названий столбцов в файле База?
И всем спасибо за ответы! :oops:  
 
 
 
#17
16.06.2019 21:52:01
.
Код
let
     a= Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\abc\cst.xlsx"), null, true)[Data]{0}, [PromoteAllScalars=true]),
     b= Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\abc\rout out.xlsx"), null, true)[Data]{0}, [PromoteAllScalars=true]),
     c= Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\abc\База.xlsx"), null, true)[Data]{0}, [PromoteAllScalars=true]),
     d = Table.SelectColumns(c&a&b, Table.ColumnNames(c))
in
     d
Изменено: artyrH - 16.06.2019 21:59:23
 
 
 
#18
16.06.2019 22:16:51
artyrH, получается две строки, хотя клиент в источнике один и тот же.
можно ли задать склеивание/совмещение столбцов cst и rout out по номеру клиента?
 
 
 
#19
17.06.2019 04:24:54
Цитата
Maayun написал:
склеивание/совмещение столбцов cst и rout out по номеру клиента
мне не понятно
 
 
 
#20
17.06.2019 06:33:56
Цитата
artyrH написал:
мне не понятно
Коллега, выше приведёт запрос на связывание таблиц из "rout out" и "cst" - NestedJoin
Maayun, для начала нужна сбивка названий столбцов из исходных файлов "rout out", "cst" с полями в "База".
Это так и должно быть, что часть полей, существующих в База, отсутствуют в исходных таблицах? Или что-то ещё?
Что делать с полем "Отдел сбыта", которое есть в обеих исходных таблицах?

По уровню понимания, того что вы делаете, более всего подходит вариант из #13, предложенный коллегой PooHkrd.
Изменено: Андрей VG - 17.06.2019 06:35:40
 
 
 
#21
17.06.2019 07:21:15
Цитата
Андрей VG написал:
запрос на связывание таблиц из "rout out" и "cst" - NestedJoin
добавил без изменений. оставил столбцы с именами из Лист1  файла База
остальное на совести тс. скорее всего надо добавить на Лист1 еще имена столбцов
Код
let
     cst= Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\abc\cst.xlsx"     ), null, true)[Data]{0}),
     #"rout out"= Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\abc\rout out.xlsx"), null, true)[Data]{0}),
   
    Источник = Table.NestedJoin(#"rout out",{"Номер клиента"},cst,{"Клиент номер"},"cst",JoinKind.LeftOuter),
    #"Другие удаленные столбцы" = Table.SelectColumns(Источник,{"Номер клиента", "Имя клиента", "Область", "Индекс", "Широта", "Долгота", "Способ прод", "cst"}),
    #"Развернутый элемент cst" = Table.ExpandTableColumn(#"Другие удаленные столбцы", "cst", {"KLABC", "Название", "ИНН", "КПП"}),
   
     
     c= Table.PromoteHeaders(Excel.Workbook(File.Contents("C:\abc\База.xlsx"    ), null, true)[Data]{0}),
     d= Table.SelectColumns (c&#"Развернутый элемент cst", Table.ColumnNames(c))
in
     d
 
 
 
#22
17.06.2019 09:02:20
Андрей VG,.доброе утро.
Да, в начале необходимо связать эти таблицы по номеру клиента - этот параметр есть и в cst, и в rout out.
artyrH, Артур, спасибо, то, что надо!
Да, необходимо добавить ещё столбцы, но с этим я справлюсь. Спасибо!
 
 
 
#23
17.06.2019 11:26:12
Ну, и мой вариант. Предполагается, что все файлы лежат в одной папке (в примере запроса лежали "C:\Path\База\"). Все листы с исходными таблицами в этих файлах имеют такое же имя как в примере "Лист1".
Код
let
    dataFolder = "C:\Path\База\",
    cst = Excel.Workbook(File.Contents(dataFolder & "cst.xlsx"), true, true){[Item="Лист1", Kind="Sheet"]}[Data],
    rout_out = Excel.Workbook(File.Contents(dataFolder & "rout out.xlsx"), true, true){[Item="Лист1", Kind="Sheet"]}[Data],
    baseCols = Excel.Workbook(File.Contents(dataFolder & "База.xlsx"), true, true){[Item="Лист1", Kind="Sheet"]}[Data],
    
    cstSetKeyType = Table.TransformColumnTypes(cst, {{"Клиент номер", Text.Type}}),
    rout_outSetKeyType = Table.TransformColumnTypes(rout_out,{{"Номер клиента", type text}}),
    rout_outSetRightName = Table.RenameColumns(rout_outSetKeyType, {"Отдел сбыта", "Отдел Сбыта"}),
    joinRout_out_cst = Table.Join(rout_outSetRightName, {"Номер клиента"}, cstSetKeyType, {"Клиент номер"}, JoinKind.LeftOuter),
    nullBaseColls = Table.FromRows({}, Table.ColumnNames(baseCols)),
    mapBaseToJoinCols = nullBaseColls & joinRout_out_cst,
    result = Table.SelectColumns(mapBaseToJoinCols, Table.ColumnNames(baseCols))
in
    result
Изменено: Андрей VG - 17.06.2019 12:23:21
 
 
 
#24
19.06.2019 21:44:27
Андрей VG, спасибо за ваш вариант)
Добавлю ещё в копилку мышкоклацательных способов перемещения в PQ.
Для этого надо сместить один столбец, жмякнуть в просмотре строку формул и редачить столбцы в fx = ...
 
 
 
#25
28.06.2019 00:01:36
Если еще актуально.
Выбирать по порядковому номеру столбцов.
Экспериментировал в свое время.
Код
(table as table, columnNumbers as list) as table =>
let
    /*
    * Функция для выбора столбцов по порядковому номеру в исходной таблице данных
    * table - набор данных
    * columnNumbers - список содержащий номера столбцов. Формат {1,2,10}, {1..12} 
    * дубликаты удаляются
    * несуществующие номера столбцов игнорируются
    * побочный эффект - столбцы в таблице расстанавливаются в порядке указанном в списке {3,1,7,24, 1..24}
    *
    */

    #"выбрать имена столбцов" = Table.ColumnNames(table),
    #"выбранные заголовки столбцов" = List.Combine(
                                        List.Transform(
                                            columnNumbers, 
                                            (x)=>List.Range(#"выбрать имена столбцов",x-1,1)
                                        )   
                                    ),
    #"удаляем дубликаты" = List.Distinct(#"выбранные заголовки столбцов"),
    #"выбираем столбцы" = Table.SelectColumns(table,#"удаляем дубликаты")
in
    #"выбираем столбцы"

Вызываем
FunctionName(NameTable, {3,1,7,24, 1..24})
Изменено: DrillPipe - 28.06.2019 00:01:57
 
 
 
#26
28.06.2019 00:21:00
В принципе конструкцию
Код
List.Combine( 
       List.Transform(columnNumbers, 
                      (x)=>List.Range(#"выбрать имена столбцов",x-1,1)
       )   
)
можно заменить
Код
List.Transform(columnNumbers, each List.Range(#"выбрать имена столбцов", _-1,1){0})
Изменено: DrillPipe - 28.06.2019 00:21:27
 
 
 
Страницы: 1
Наверх