Страницы: 1
RSS
Power Query. Выбор строк с самыми актуальными данными, в таблице присутствуют повторы, оставить только самые актуальные
 
Здравствуйте! Прошу помочь с решением задачи. Только начинаю изучать PQ.

Дано:
Папка в которую закидываются файлы с именем по типу ГГГГ.ММ.ДД.
Все файлы содержат единственное опорное поле "ID".
В каждом последующем файле могут быть изменены значения в столбце "Сумма в валюте сделки".
В каждом последующем файле может увеличиваться количество ID, т.е. файлы сравнивать не нужно

Необходимо:
Собрать все данные только в одну таблицу и оставить записи только из самых последних файлов, см. рис. 1.
Прикладываю примеры файлов и таблицу с PQ. Если папку положить просто на диск C, то PQ должен работать корректно.

Буду благодарен за описание шагов.
Изменено: ivanka - 16.07.2019 15:49:47 (добавил рисунок)
 
ivanka, трудно понять что нужно
Код
let
    Source = Folder.Files("C:\abc\пример (1)\пример\пример"),
    #"Added Custom" = Table.AddColumn(Source, "Пользовательская", each Excel.Workbook([Content])),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom", "Пользовательская", {"Data"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded {0}", "Data2", each Table.PromoteHeaders([Data], [PromoteAllScalars=true]))[[Name],[Data2]],
    #"Expanded {0}1" = Table.ExpandTableColumn(#"Added Custom1", "Data2", {"ID", "Название", "Сумма в валюте сделки"}),
    #"Reversed Rows" = Table.ReverseRows(#"Expanded {0}1"),
    #"Removed Duplicates" = Table.Distinct(#"Reversed Rows", {"ID", "Название", "Сумма в валюте сделки"}),
    #"Reversed Rows1" = Table.ReverseRows(#"Removed Duplicates")
in
    #"Reversed Rows1"
или так
let
    Source = Folder.Files("C:\abc\пример (1)\пример\пример"),
    #"Added Custom" = Table.AddColumn(Source, "Пользовательская", each Excel.Workbook([Content])),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Added Custom", "Пользовательская", {"Data"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded {0}", "Data2", each Table.PromoteHeaders([Data], [PromoteAllScalars=true]))[[Name],[Data2]],
    #"Expanded {0}1" = Table.ExpandTableColumn(#"Added Custom1", "Data2", {"ID", "Название", "Сумма в валюте сделки"}),
    #"Reversed Rows" = Table.ReverseRows(#"Expanded {0}1"),
    #"Removed Duplicates" = Table.Distinct(#"Reversed Rows", {"ID"}),
    #"Reversed Rows1" = Table.ReverseRows(#"Removed Duplicates")
in
    #"Reversed Rows1"
Изменено: artyrH - 16.07.2019 16:05:28
 
Вариант:
Код
let
    Source = Folder.Files("C:\пример"),
    AddTabs = Table.AddColumn( Source, "Tabs", each Table.PromoteHeaders( Excel.Workbook( File.Contents( [Folder Path] & [Name] ), null, true ){0}[Data], [PromoteAllScalars=true] ) ),
    SelectedColumns = Table.SelectColumns(AddTabs,{"Name", "Tabs"}),
    ReplacedValue = Table.ReplaceValue(SelectedColumns,"..xlsx","",Replacer.ReplaceText,{"Name"}),
    Expanded = Table.ExpandTableColumn(ReplacedValue, "Tabs", {"ID", "Название", "Сумма в валюте сделки"}),
    ChangedType = Table.TransformColumnTypes(Expanded,{{"Name", type date}, {"ID", Int64.Type}, {"Название", type text}, {"Сумма в валюте сделки", type number}}),
    GroupedRows = Table.Group(ChangedType, {"ID"}, {{"Tab", each Table.Max(_, "Name"), type record}}),
    Expanded2 = Table.ExpandRecordColumn(GroupedRows, "Tab", {"Name", "Название", "Сумма в валюте сделки"})
in
    Expanded2
Изменено: PooHkrd - 16.07.2019 16:45:01 (исправил запрос, предыдущий был с глюком)
Вот горшок пустой, он предмет простой...
 
Цитата
artyrH написал:
Код
Table.Distinct(#"Reversed Rows", {"ID", "Название", "Сумма в валюте сделки"})
Только наверное это надо заменить на просто:
Код
Table.Distinct(#"Reversed Rows", {"ID"})

И стоит добавить влоб сортировку по дате (вместо реверса), кто его знает в каком порядке произойдет импорт файлов...

Изменено: vetrintsev - 16.07.2019 16:15:39
 
Коллеги, а зачем все эти реверсы и сортировки? Вроде бы ТС просил банальную фильтрацию? Или я чего-то пропустил?
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал: Или я чего-то пропустил?
ТС просит
Цитата
ivanka написал:  оставить записи только из самых последних файлов,
А файлы именуются
Цитата
ivanka написал: файлы с именем по типу ГГГГ.ММ.ДД
 
Да, мне нужна просто фильтрация. Как этот вопрос вы решили бы в Excel? Если нужно взять конкретный ID, найти в столбце все аналогичные значения и проверить какие даты стоят напротив этих одинаковых ID. Напротив ID с самой актуальной датой можно поставить цифру 1.  
 
Доброе время суток
Цитата
PooHkrd написал:
Table.Group(ChangedType, {"ID"}, {{"Tab", each Table.SelectRows(_, let latest = List.Max(ChangedType[Name]) in each [Name] = latest), type table}})
Алексей, а не проще ли
Код
Table.Group(ChangedType, {"ID"}, {{"Tab", each Table.Max(_, [Name]), type record}})
С последующим разворачиванием столбца с типом запись?
 
Цитата
artyrH написал:
ivanka , трудно понять что нужно
Нужно взять конкретный ID, найти в столбце "ID" все аналогичные значения и проверить какие даты стоят напротив этих одинаковых ID. Напротив ID с самой актуальной датой можно поставить цифру 1.  
Как вы решили бы данный вопрос c помощью формул?

Спасибо!
 
Андрей VG,  как реализовать ваше решение в PQ? Нужно внести эту строку в код? Я просто давил в самом конце и была ошибка.
 
Андрей VG, да забыл про него как всегда.
Причем этот вариант корректно отработал, предыдущий не правильно считает, по ходу опять вылезает тот самый глюк, что не желательно комбинировать фильтрацию и последующее разворачивание столбцов. С разворачиванием записей ошибок нету.
Цитата
ivanka написал:
как реализовать ваше решение в PQ?
Корректный текст запроса внес в исходный пост #3.
Изменено: PooHkrd - 16.07.2019 16:46:06
Вот горшок пустой, он предмет простой...
 
Я бы ещё и с датами по аккуратнее. В общем ещё один вариант
Код
let
    filePath = "d:\Path\пример\пример",
    folderFiles = Table.SelectRows(Folder.Files(filePath), each (not Text.StartsWith([Name], "~$")) and Text.Lower([Extension]) = ".xlsx"),
    getDateFromName = Table.AddColumn(folderFiles, "Date", each Date.From(Text.BeforeDelimiter([Name], ".", 2)), Date.Type)[[Date], [Content]],
    getExcelContent = Table.TransformColumns(getDateFromName, {"Content", each Excel.Workbook(_)}),
    getFirstSheetData = Table.TransformColumns(getExcelContent, {"Content", (item) => Table.SelectRows(item, each [Kind] = "Sheet"){0}[Data]}),
    setHeaderByFirstRow = Table.TransformColumns(getFirstSheetData, {"Content", each Table.PromoteHeaders(_)}),
    toCommonTable = Table.ExpandTableColumn(setHeaderByFirstRow, "Content", {"ID", "Название", "Сумма в валюте сделки"}),
    getLatestById = Table.Group(toCommonTable, {"ID"}, {"temp", each Table.Max(_, "Date")})[[temp]],
    #"Expanded {0}" = Table.ExpandRecordColumn(getLatestById, "temp", {"Date", "ID", "Название", "Сумма в валюте сделки"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded {0}",{{"Date", type date}, {"ID", type text}, {"Название", type text}, {"Сумма в валюте сделки", Currency.Type}})
in
    #"Changed Type"
 
Андрей VG, благодарю, всё работает! Андрей VG, подскажите пожалуйста, как это решить инструментами интерфейса?
Формулу я точно не запомню.
 
Цитата
ivanka написал:
как это решить инструментами интерфейса?
увы, тут я начинающий, путаюсь я в этих кнопках и версиях интерфейса :(
 
Цитата
PooHkrd написал:
зачем все эти реверсы
а чем мой вариант не решение? задачу выполняет. была бы возможность в Distinct удалять дубликаты с нижних строк выбранных столбцов, я бы не использовал бы реверс.
 
Цитата
artyrH написал:
а чем мой вариант не решение?
Тем что он зависит от изначального порядка файлов, который не очевиден, т.е. в любом случае нужна предварительная сортировка, а если мы можем задавать порядок сортировки, та зачем там реверс? Короче говоря, на трех файлах в одном месяце вроде работает, когда пойдут файлы пачками то обязательно вылезет какой-нибудь косяк.
Вот горшок пустой, он предмет простой...
 
ivanka,  если есть возможность, скиньте на диск файлов 20 - хочу косяк поймать
 
artyrH, 20 это ни о чем, вот архив года за 3 лопатить, вот это да!
Вот горшок пустой, он предмет простой...
 
PooHkrd, вот не убедили вы меня. в файле вариант с реверс такой же рабочий, как и остальные
 
Цитата
artyrH написал:
вот не убедили вы меня
А я такой цели и не ставил. Просто высказал мнение по поводу вашего подхода и заодно поставил в известность ТС о своих сомнениях.
А какие выводы делать - каждый сам себе злобный буратино.  :D
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх