Блог

В настоящее время я получаю много данных из моей базы данных CosmosDB, которые я хочу сократить до последних 8 недель.

Как я могу фильтровать в PowerQuery, чтобы получить последние 8 недель на основе моего столбца даты.

Это мой powerquery для получения данных:

 let
    Source = DocumentDB.Contents("https://xxx.xxx", "xxx", "xxx"),
    #"Expanded Document" = Table.ExpandRecordColumn(Source, "Document", {"$v"}, {"Document.$v"}),
    #"Expanded Document.$v" = Table.ExpandRecordColumn(#"Expanded Document", "Document.$v", {"date"}, {"Document.$v.date"}),
    #"Expanded Document.$v.date" = Table.ExpandRecordColumn(#"Expanded Document.$v", "Document.$v.date", {"$v"}, {"Document.$v.date.$v"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Document.$v.date",{{"Document.$v.date.$v", type text}})
in
    #"Changed Type"
 

И вот как данные находятся в моей базе данных CosmosDB:

 {
"_id" : ObjectId("5c6144bdf7ce070001acc213"),
"date" : {
    "$date" : 1549792055030
},
 

Если вы хотите выполнить всю работу со своей стороны (возможно, сервер может выполнить некоторые / все это):

• Предполагая 1549792055030 , что (показано в примере) — это временная метка Unix, выраженная в миллисекундах, для преобразования в datetime запрос in Power попробуйте что-то вроде: #datetime(1970, 1, 1, 0, 0, 0) #duration(0, 0, 0, 1549792055030/1000)
• Кажется, вы расширяете поле записи с именем $v (которое само было вложено в поле с именем date , которое само было вложено в поле с именем $v ) в вашем M коде, но $v не отображается как присутствующее в структуре. Я упоминаю об этом, поскольку непонятно, следовать ли вашему M коду или структуре. Я собираюсь предположить, что у вас есть $v поле, которое содержит date поле, которое само содержит $date поле. Чтобы получить вложенную временную метку Unix, вы можете попробовать что-то вроде: someRecord[#"$v"][date][#"$date"]
• Поскольку вас интересуют только последние 8 недель, вы можете протестировать что-то вроде: Date.IsInPreviousNWeeks(DateTime.AddZone(someDatetime, 0), 8) . (Вы также можете сделать это другим способом, преобразовав 8 weeks ago before now в временную метку Unix, а затем отфильтровав временные метки >= по полученному вами значению.)

Собрав все вышесказанное вместе, мы могли бы получить некоторый M код, который выглядит следующим образом:

 let
    Source = DocumentDB.Contents("https://xxx.xxx", "xxx", "xxx"),
    filterDates = Table.SelectRows(Source, each
        let
            millisecondsSinceEpoch = Number.From([document][#"$v"][date][#"$date"]),
            toDatetime = #datetime(1970, 1, 1, 0, 0, 0)   #duration(0, 0, 0, millisecondsSinceEpoch/1000),
            toFilter = Date.IsInPreviousNWeeks(DateTime.AddZone(toDatetime, 0), 8)
        in toFilter
    )
in filterDates
 

Приведенный выше код может быть функциональным (надеюсь), но концептуально это может быть неправильный способ сделать это. Я не знаком с этой функцией DocumentDB.Contents , но эта ссылка (https://www.powerquery.io/accessing-data/document-db/documentdb.contents ) предполагает, что он имеет следующие параметры:

функция (url в виде текста, необязательная база данных как обнуляемая any, необязательная коллекция как обнуляемая any, необязательные параметры как обнуляемая запись) в виде таблицы

и далее говорится:

если запрос поля указан в записи параметров, будут возвращены результаты запроса, выполняемого в указанной базе данных и / или коллекции.

Я понимаю, что это означает, что если вы измените свою первую строку на что-то вроде:

 Source = DocumentDB.Contents("https://xxx.xxx", "xxx", "xxx", [Query = "..."])
 

если указанный вами запрос "..." будет понят сервером (предположим, что запрос должен быть на родном языке запросов Cosmos DB), вам будут возвращены данные только за последние 8 недель (что означает, что требуется меньше данных для отправки и меньше работы для вас).). Как я уже сказал, я не знаком с Azure Cosmos DB, поэтому я не могу комментировать дальше. Но это кажется лучшим способом сделать это.