Страницы: 1 2 След.
RSS
Создание общей таблицы в PQ из данных, которые есть в текущей таблице
 
Добрый день!
Есть журнал проводок где указано построчно счет дебета, счет кредита и одна общая сумма.
Нам нужно собрать сводную в виде списка счетов и аналитики к ним, а вот сумму можно представить в колонках дебет и кредит (ну или свернуто в одной Дебет с плюсом и Кредит с минусом - если такой вариант легче реализовать)
Желательно инструментами power query  или   power pivot.
Это реально сделать этими инструментами?
Буду признательна за совет.  
 
Цитата
Solomama написал:
Это реально
реально
мышкоклацный вариант:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Счет Дт", "Счет Кт", "Сумма"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Сумма"}, "Атрибут", "Значение"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Атрибут", each Text.AfterDelimiter(_, " "), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Text After Delimiter", List.Distinct(#"Extracted Text After Delimiter"[Атрибут]), "Атрибут", "Сумма", List.Sum)
in
    #"Pivoted Column"
вот только с вашим примером не бьётся от слова совсем - откуда аренда 900 по 91.01?
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
buchlotnik написал:
мышкоклацный вариант:
Это значит не надо писать коды, а просто на кнопочки power query клацать?))
По поводу результата - это структура отчета визуально. Суммы наобум поставила.
Спасибо большое, с ноута посмотрю детальный файл. Там запрос наверно ж остался. А субконто нельзя проставить? Только счет?  
Изменено: Solomama - 21.06.2020 15:46:52
 
Цитата
Solomama написал:
нельзя проставить?
можно проставить всё что угодно, какой пример, такой и ответ, т.е.
Цитата
Solomama написал:
наобум
Соблюдение правил форума не освобождает от модераторского произвола
 
Поняла, спасибо!)  
 
Solomama,в примере точнее покажите что должно получиться. В ячейке b1 что? Значения для фильтра?
 
Михаил Л,
Изменила.
Посмотрите, пожалуйста, надеюсь понятней стало.
Изменено: Solomama - 21.06.2020 20:04:30
 
buchlotnik, посмотрела запрос, вы развернули столбцы со счетами, а потом собрали, но в другой структуре верно?
Дело в том, что у каждого счета своя аналитика. У меня не получилось собрать табличку, после разворота суммы, если параметры: Счет Дт, Субконто Дт, Счет Кт, Субконто Кт, Сумма.

Вроде поняла как сделать (более менее), осталась проблема с субконто (2 столбца получилось, а надо один).
Не подскажете как исправить?

UPD: Получилось через условный столбец, однако мне кажется можно было сделать проще.
Если есть идеи, пожалуйста, подкиньте)
 
Цитата
Solomama написал:
надеюсь понятней стало
Не стало. Пупкин появился
Думаю, доделать сможете
Код
// Табл1
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Group = let a=Source,b=Table.Group(a, {"Счет Дт","Субконто1 Дт"}, 
    {{"сумма", each List.Sum([Сумма]), type number}}),c=Table.Group(a, 
    {"Счет Кт","Субконто1 Кт"}, {{"сумма", each 0-List.Sum([Сумма]), 
    type number}}),d=Table.RenameColumns(b,{{"Субконто1 Дт", "Субконто1"},
    {"Счет Дт", "Счет"}}),e=Table.RenameColumns(c,{{"Субконто1 Кт", 
    "Субконто1"}, {"Счет Кт", "Счет"}}) in d&e,
    Filter=Table.SelectRows(Group, each ([Счет]="26" or [Счет]="91.01"))
in
    Filter

// Табл2
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Group = let a=Source,b=Table.Group(a, {"Счет Дт","Субконто1 Дт"}, 
    {{"сумма", each List.Sum([Сумма]), type number}}),c=Table.Group(a, 
    {"Счет Кт","Субконто1 Кт"}, {{"сумма", each List.Sum([Сумма]), 
    type number}}),d=Table.RenameColumns(b,{{"Субконто1 Дт", "Субконто1"},
    {"Счет Дт", "Счет"}, {"сумма", "Сумма Дт"}}),e=Table.RenameColumns(c,{{"Субконто1 Кт", 
    "Субконто1"}, {"Счет Кт", "Счет"}, {"сумма", "Сумма Кт"}}) in d&e,
    Filter=Table.SelectRows(Group, each ([Счет]="26" or [Счет]="91.01"))
in
    Filter
 
Михаил Л, Благодарю)
 
для разнообразия (формируем источник для сводной) Табл2:
Скрытый текст
и для варианта Табл1:
Скрытый текст
Изменено: buchlotnik - 22.06.2020 00:53:37
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, спасибо большое!)  
 
Solomama, слегка поправил код (см. в пред. собщении)
Соблюдение правил форума не освобождает от модераторского произвола
 
Кто-нибудь сможет ответить почему запрос не выгружается на лист?
Уже все удалил
Хотя в примере из сообщения #7 выгружается
В версии  PQ - 2.59.5135.201 в функции Table.FromRecords нет третьего аргумента
 
какой-то глюк - мне написал, что запрос подключен к модели данных и для загрузки от модели его надо отключить - после ОК - загрузился
Цитата
Михаил Л написал:
В версии  PQ - 2.59.5135.201 в функции Table.FromRecords нет третьего аргумента
да, третий аргумент - недавняя фишка, собственно костыль вы правильный примотали - мне вчера уже лень было
Соблюдение правил форума не освобождает от модераторского произвола
 
Цитата
buchlotnik написал:
вы правильный примотали
Почему сразу костыль :)  Это возможности PQ
У нас же запросы состоят не из костылей
Как отключить от модели данных - не нашел, но при дублировании запроса все выгружается как надо
Изменено: Михаил Л - 22.06.2020 14:48:38
 
Михаил Л, а не подскажите как вы группировали? Это ручками код писали или кнопочками? У меня шестеренка не открылась, понять не могу, а очень интересно. Всего три шага)
 
buchlotnik,
А вы тоже код ручками писали?
Такая красивая презентация информации получилась.
Придется учиться писать коды) Так самой не сделать мне конечно!

P.S. Так странно, по коду вроде запрос, а дважды щелкнешь - список разворачивается! Это очень удобно! А подскажите плиз подробней все таки что это и как это делать (если не сложно конечно).
Изменено: Solomama - 22.06.2020 15:21:42
 
Цитата
Solomama написал:
ручками код писали или кнопочками?
И кнопочками и ручками
Понажимаю кнопочками и собираю в общую картину
Код
// Запрос1
let a=Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    b=Table.Group(a, {"Счет Дт","Субконто1 Дт"},{{"сумма", each List.Sum([Сумма]), type number}}),
    c=Table.Group(a,{"Счет Кт","Субконто1 Кт"}, {{"сумма", each 0-List.Sum([Сумма]),type number}}),
    d=Table.RenameColumns(b,{{"Субконто1 Дт", "Субконто1"},{"Счет Дт", "Счет"}}),
    e=Table.RenameColumns(c,{{"Субконто1 Кт", "Субконто1"}, {"Счет Кт", "Счет"}}),
    f=d&e
in  f
 
Михаил Л,
Вы сформировали шаг b, по аналогии шаг с начала формировать и excel выдает ошибку. Видимо в шаге b уже этих данных та нет. Пыталась в коде прописать вместо Дт Кт к примеру, тоже не прокатило.
Поделитесь секретом, как вам удалось?
И с f шагом, это префикс и все? еще не дошла до него, но думаю будет затык.
 
Цитата
Solomama написал:
по аналогии шаг с начала формировать и excel выдает ошибку. Видимо в шаге b уже этих данных та нет
Попробуйте добавить после Table.Group(    вместо имеющегося a. Получится Table.Group(a,{...
 
Михаил Л,
Да, получилось.
А c f получается вы префикс сделали, чтоб шаг появился, а потом переписали его на соединение d и e, да?)
Прикольно!
Спасибо)
 
Цитата
Solomama написал:
А c f получается вы префикс сделали, чтоб шаг появился, а потом переписали его на соединение d и e, да?)
Не понял про префикс.
Просто нажал Добавить шаг и написал в нем d & e . Амперсанд соединяет шаги
Код
let d={"a","b","c"},
    e={"d","c","b","a"},
    f=d  &  e
in
    f
 
Цитата
Solomama написал:
buchlotnik , А вы тоже код ручками писали?
да, разумеется
Цитата
Solomama написал:
не разобралась с файлом где таинственный третий аргумент
что именно не идёт? покажите в файле
Изменено: buchlotnik - 22.06.2020 16:52:51
Соблюдение правил форума не освобождает от модераторского произвола
 
Код
 let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    lst0 = {"Организация", "Сумма", "Счет Дт", "Субконто1 Дт", "Счет Кт", "Субконто1 Кт"},
    lst1 = {"Организация","Счет","Субконто","СуммаДТ","СуммаКТ"},
    trnsf = Table.CombineColumns(from,lst0,
                                            (l)=>{  [Организация = l{0}, Счет=l{2},Субконто=l{3},СуммаДТ=l{1}],
                                                    [Организация = l{0},Счет=l{4},Субконто=l{5},СуммаКТ=l{1}]},"tmp")[tmp],
    tolst = List.Combine(trnsf),
    to = Table.FromRecords(tolst,lst1,MissingField.UseNull)
in
    to

@buchlotnik, добрый день)
можете, пожалуйста, подсказать, почему при выполнении запроса по данному коду выходит следующее сообщение:
Цитата
Expression.Error: Аргументы 3 были переданы функции, которая ожидает значения между 1 и 2.
Сведения:
   Pattern=
   Arguments=List
что оно означает?
 
Доброе время суток
Цитата
Alyona написал:
что оно означает?
Это означает, что у вас старая версия Power Query, которая не поддерживает в функции Table.FromRecords появившийся третий аргумент optional missingField as nullable number. Попробуйте обновиться.
 
Андрей VG, спасибо! Попробовала выполнить в другой версии - получилось)
 
buchlotnik,
я пытаюсь повторить ваш код, но добавив Дату.
Выходит такая ошибка:

Expression.Error: Столбец "Субконто" таблицы не найден.
Сведения:
   Субконто

При этом если убираю Субконто, такая же ошибка переносится на Счет

Сам код:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    d = Table.RemoveColumns(Source,{"N", "Документ", "Кол.", "Количество Дт", "Валюта Дт", "Вал. сумма Дт", "Подразделение Дт", "Субконто2 Дт", "Субконто3 Дт", "Кол.2", "Количество Кт", "Валюта Кт", "Вал. сумма Кт", "Подразделение Кт", "Субконто2 Кт", "Субконто3 Кт", "Содержание", "НУ", "ПР", "ВР", "Сумма НУ Дт", "Сумма ПР Дт", "Сумма ВР Дт", "Сумма НУ Кт", "Сумма ПР Кт", "Сумма ВР Кт"}),
    a = {"Дата", "Организация", "Счет Дт", "Счет Кт", "Сумма", "Субконто1 Дт", "Субконто1 Кт"},
    b = {"Дата", "Организация", "Счет","Сумма Дт", "Сумма Кт", "Субконто"},
    c = Table.CombineColumns(d, b, 
                                                        (1)=>{  [Организация = 1{1}, Счет = 1{2}, Сумма = 1{4}, Субконто = 1{5}],
                                                            [Организация = 1{1}, Счет = 1{3}, Сумма = -1{4}, Субконто = 1{6}]},"tmp")[tmp],
    tolst = List.Combine(c),
    to = Table.FromRecords(tolst,b,MissingField.UseNull)
in
    to
Может подскажете, что не так?
И если не сложно, можете объяснить, что означают символы в коде (1)=> и "tmp")[tmp] ? Не нашла в интернете объяснения.
Заранее благодарю.
Изменено: Solomama - 23.06.2020 23:56:37
 
Михаил Л,
я имела ввиду, что на шаге f когда нажимаешь шестеренку, указана команда Префикс.
Я уже поняла как добавлять шаги.
Спасибо за идею)
 
Цитата
Solomama написал:
пытаюсь повторить ваш код
которую версию? вижу сборную солянку
Цитата
Solomama написал:
Выходит такая ошибка:
разумеется, у вас в исходной таблице НЕТ этого столбца, система не может его обработать, счета это также касается
Цитата
Solomama написал:
что означают символы в коде
(аргументы)=> обозначает функцию
#table[Нужное поле] означает взять из таблицы список значений нужного поля (столбца)

а теперь объясните - зачем вы создали список а если в коде его не используете, зачем добавили шаг d - в принципе такие задачи нужно решать через Table.SelectColumns, но здесь-то это не нужно, поскольку )[tmp]
да, и аргумент - у меня не единица, а английская эль - имена не могут с цифр начинаться, короче заменил на х
рабочий запрос выглядит примерно так:
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    //d = Table.RemoveColumns(Source,{"N", "Документ", "Кол.", "Количество Дт", "Валюта Дт", "Вал. сумма Дт", "Подразделение Дт", "Субконто2 Дт", "Субконто3 Дт", "Кол.2", "Количество Кт", "Валюта Кт", "Вал. сумма Кт", "Подразделение Кт", "Субконто2 Кт", "Субконто3 Кт", "Содержание", "НУ", "ПР", "ВР", "Сумма НУ Дт", "Сумма ПР Дт", "Сумма ВР Дт", "Сумма НУ Кт", "Сумма ПР Кт", "Сумма ВР Кт"}),
    a = {"Дата", "Организация", "Счет Дт", "Счет Кт", "Сумма", "Субконто1 Дт", "Субконто1 Кт"},
    //b = {"Дата", "Организация", "Счет","Сумма Дт", "Сумма Кт", "Субконто"},
    c = Table.CombineColumns(from, a, 
                                                        (x)=>{  [Дата =  x{0}, Организация = x{1}, Счет = x{2}, Сумма = x{4}, Субконто = x{5}],
                                                            [Дата = x{0}, Организация = x{1}, Счет = x{3}, Сумма = -x{4}, Субконто = x{6}]},"tmp")[tmp],
    tolst = List.Combine(c),
    to = Table.FromRecords(tolst)
in
    to
Изменено: buchlotnik - 24.06.2020 09:31:01
Соблюдение правил форума не освобождает от модераторского произвола
Страницы: 1 2 След.
Наверх