Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 5 6 7 8 9 След.
Фильтрация вложенной таблицы Power Query
 
PooHkrd, приветствую! Надо же, ещё помнят :) Дефицит интересных задач, вот восполняю :) А то с вашими телеграмьими скоростями едва листать успеваешь ;)  
Фильтрация вложенной таблицы Power Query
 
Цитата
написал:
Или я неверно понял то, что Вы написали ?
Похоже на то :) Впрочем, я тоже немного ошибся - особенно не смотрел Ваши данные, думал, дата и время будет уникальным идентификатором. Если уникальный идентификатор у Вас в столбце RN, то нужно добавить его в ORDER BY:
Код
let
  Source = Sql.Database("Server", "Database"), 
  sql = Value.NativeQuery(
    Source, 
    "SELECT *,
            SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date, RN) as RT 
    FROM
        dbo.Table", 
    null, 
    [EnableFolding = true]
  )
in
  sql

Ну или сделать более универсально (т.е. даже если уникального идентификатора строки нет):
Код
let
  Source = Sql.Database("Server", "Database"), 
  sql = Value.NativeQuery(
    Source, 
    "SELECT *,
            SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RT
    FROM
        dbo.Table", 
    null, 
    [EnableFolding = true]
  )
in
  sql

В целом, код выполняет ровно то, что Вы и хотели:
1. Добавляет столбец с накопительным итогом по каждому продукту
2. Делает это в PQ
3. Так, чтобы query folding не поломался.
Изменено: Aleksei_Zhigulin - 13.07.2022 17:50:05
Фильтрация вложенной таблицы Power Query
 
Можно так (query folding сохраняется):
Код
let
  Source = Sql.Database("Server", "Database"), 
  sql = Value.NativeQuery(
    Source, 
    "SELECT *,
            SUM(Price) OVER (PARTITION BY ProductID ORDER BY Date) as RT 
    FROM
        dbo.Table", 
    null, 
    [EnableFolding = true]
  )
in
  sql

Server, Database, Table (и схему, если не dbo) подставляете свои. Вместо * можете выбрать только интересующие столбцы. Потестил с SQL Server 2019, работает. Если в Вашей базе данных нет оконок, можете посмотреть альтернативы.
Изменено: Aleksei_Zhigulin - 13.07.2022 00:11:40
Не обновляется запрос PQ
 
Понятно, невнимательно прочитал, думал, во вложении тот же файл, что и по ссылке. Тогда просто проверьте правильность написания пути, оба запроса работают. Если вдруг файл не Ваш, и под некорректной работой запроса Вы имеете в виду возникающие при обновлении запроса проблемы с макросами / пользовательскими функциями VBA, то можно просто пересохранить файл ВРУ КЧ 6-10.xlsm в .xlsx.
Не обновляется запрос PQ
 
Добрый день.
К несуществующей таблице обращаетесь:
Изменено: Aleksei_Zhigulin - 11.07.2022 18:50:14
Как сделать в Power Query Источник = текущая книга
 
Цитата
Иван Иванов написал:
легкого решения нет
Ну, не rocket science:

1. На каком-нибудь вспомогательном листе прописываем формулу:
Код
= CELL("filename";A1)
Код
= ЯЧЕЙКА("имяфайла";A1)
2. Присваиваем ячейке имя filename

3. Пишем запрос path:
Код
let
    Source = Excel.CurrentWorkbook(),
    filename = Source{[Name="filename"]}[Content],
    path = let a = Text.SplitAny(filename{0}[Column1],"[]") in a{0} & a{1}
in
    path
4. Теперь в другом запросе используем path в качестве пути к книге:
Код
Source = Excel.Workbook(File.Contents(path))

При желании путь можно получить сразу на первом шаге - up to you:
Код
=LET(a; CELL("filename";A1); b; SEARCH("[";a); LEFT(a;b-1))
Вычитание нулевого значения в PQ, Разность между двумя полями (умкньшаемое=десятичное число, вычитаемое = null, разница = null
 
Цитата
buchlotnik написал:
просто констатация, что механизм не эквивалентный
интересно, спасибо
Вычитание нулевого значения в PQ, Разность между двумя полями (умкньшаемое=десятичное число, вычитаемое = null, разница = null
 
Цитата
buchlotnik написал:
в скорости проигрывает
существенно?
Вычитание нулевого значения в PQ, Разность между двумя полями (умкньшаемое=десятичное число, вычитаемое = null, разница = null
 
buchlotnik, на всякий случай, вдруг не в курсе:
Код
if x = null then 0 else x

теперь можно записывать вот так (в Office 365, Power BI):
Код
x ?? 0
Изменено: Aleksei_Zhigulin - 11.04.2021 18:34:42
Вычитание нулевого значения в PQ, Разность между двумя полями (умкньшаемое=десятичное число, вычитаемое = null, разница = null
 
Или используйте List.Sum для суммирования / вычитания столбцов, где могут быть null.
Power Query - умножение каждого значения из многих строк и столбцов на константу по условию
 
Всё-таки неисповедимы пути оптимизации в PQ. Пытался что-то сварганить с List.Generate - всё равно запрос выполнялся процентов на 60 дольше, чем у Андрей VG. Сделал элементарно - всё летает. Чудеса-чудеса - небывальщина...

Вариант без сортировки строк:
Код
let
  Source = data,
  cols = List.Buffer(List.Skip(Table.ColumnNames(Source))),
  final = Table.TransformColumns(
    Table.SelectRows(Source, each [#"TRUE/FALSE"]),
    List.Transform(cols, (x) => {x, each _ * 10})
  )
    & Table.TransformColumns(
      Table.SelectRows(Source, each not [#"TRUE/FALSE"]),
      List.Transform(cols, (x) => {x, each _ * 20})
    )
in
  final

Чуть медленнее - с сортировкой:
Код
let
  Source = data,
  i = Table.AddIndexColumn(Source, "i"),
  cols = List.Buffer(List.RemoveItems(Table.ColumnNames(i), {"TRUE/FALSE", "i"})),
  transform = Table.TransformColumns(
    Table.SelectRows(i, each [#"TRUE/FALSE"]),
    List.Transform(cols, (x) => {x, each _ * 10})
  )
    & Table.TransformColumns(
      Table.SelectRows(i, each not [#"TRUE/FALSE"]),
      List.Transform(cols, (x) => {x, each _ * 20})
    ),
  final = Table.RemoveColumns(Table.Sort(transform, {"i", 0}), "i")
in
  final
Изменено: Aleksei_Zhigulin - 06.03.2021 01:52:54
Power Query. Завернуть несколько щагов запроса в пользовательскую функцию
 
Вариант:
Код
(tbl, col, n)=>
let
    z = Table.RenameColumns(tbl,{col,"Свойство"})
in
    Table.ReplaceValue(z,"*",")",(a,b,c)=>b & {"Нет","Да"}{Number.From(a > n)} & c, {"Свойство"})
Изменено: Aleksei_Zhigulin - 02.03.2021 19:48:45
Pq Замена последнее не пустое по столбцу на null
 
Ещё вариант:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    cols = Table.ColumnNames(Source),
    f = (l,n)=>List.RemoveLastN(l,n),
    x = Record.ToList(Source{0}),
    list = f(f(x, each _ = null),1),
    result = #table(cols,{}) & #table(List.FirstN(cols,List.Count(list)),{list})
in
    result

Возможно, с List.Buffer на шагах cols и list было бы чуток быстрее.

Изменено: Aleksei_Zhigulin - 26.02.2021 00:36:45
Power BI - порядок сортировки столбцов
 
Не исключено, что есть более цивилизованный способ, но как вариант (если год выводится из таблицы дат):
1. Добавляем в таблицу дат вспомогательный столбец:
Код
ForSorting = DIVIDE(1,YEAR('Calendar'[Date]))
2. Выделяем столбец с годом, нажимаем Сортировать по столбцу и выбираем созданный в п.1 столбец:
Изменено: Aleksei_Zhigulin - 24.02.2021 19:46:47
PowerPivot, DAX для Excel 2013, Получение суммы определенных клиентов
 
Цитата
PooHkrd написал:
если верить  этому , DAX появился в SSAS еще в 2009
Спасибо, по этой статье действительно можно так подумать, хотя там этого прямо и не утверждается. Встречал в разных источниках что сначала было слово появился Power Pivot, потом уже эта технология перекочевала в SSAS (Tabular model появилась в SQL Server 2012). Это в некотором роде "разрыв шаблона", потому и запомнил :)
Изменено: Aleksei_Zhigulin - 17.02.2021 19:32:28
PowerPivot, DAX для Excel 2013, Получение суммы определенных клиентов
 
Цитата
Андрей VG написал:
Для EXCEPT это 2016, а для CALCULATE это 2012.
Андрей, спасибо! Про CALCULATE думал, она вообще в DAX врождённая :)

Цитата
Андрей VG написал:
Вряд ли в Excel появилось раньше чем в SSAS Tabular
Странно, насколько я знаю, DAX сперва появился в Power Pivot и только потом в SSAS. Но если это только про EXCEPT, то вопросов нет :)
PowerPivot, DAX для Excel 2013, Получение суммы определенных клиентов
 
Андрей VG, мне казалось, функция давнишняя, но похоже ошибся, с 2016: https://dax.guide/except/\

P.S. Хотя там и CALCULATE для Excel 2016 указан, так что нужен более надёжный источник :)
Изменено: Aleksei_Zhigulin - 15.02.2021 20:30:42
PowerPivot, DAX для Excel 2013, Получение суммы определенных клиентов
 
Ещё вариант:
Код
=
CALCULATE (
    SUM ( Data[Сумма] );
    EXCEPT (
        VALUES ( Data[Клиент] );
        CALCULATETABLE (
            VALUES ( Data[Клиент] );
            Data[Заказчик] = "A"
                || Data[Заказчик] = "D"
        )
    )
)
Dax. Рассчёт доли по группе, сумма которых 100%
 
Добавлю ещё "итальянский" вариант (ISINSCOPE нет в Excel 2016, так что в PBI):
Код
PercOnParent = 
VAR CurrentSales = [SalesAmount]
VAR TradeMarkSales =
    CALCULATE ( [SalesAmount], ALLSELECTED ( 'Товар'[Наименование товара] ) )
VAR RetailChainSales =
    CALCULATE ( [SalesAmount], ALLSELECTED ( 'ТМ'[Торговая Марка] ) )
VAR TotalSales =
    CALCULATE ( [SalesAmount], ALLSELECTED ( 'Магазин'[Сеть] ) )
VAR RatioToParent =
    IF (
        ISINSCOPE ( 'Товар'[Наименование товара] ),
        DIVIDE ( CurrentSales, TradeMarkSales ),
        IF (
            ISINSCOPE ( 'ТМ'[Торговая Марка] ),
            DIVIDE ( CurrentSales, RetailChainSales ),
            IF ( ISINSCOPE ( 'Магазин'[Сеть] ), DIVIDE ( CurrentSales, TotalSales ) )
        )
    )
RETURN
    RatioToParent
Power Query: признак группы по возрасту для дальнейшей обработке в Power BI
 
Как вариант:
Код
let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
  age = Table.AddColumn(
    Source,
    "Age",
    each 
      let
        a = DateTime.FixedLocalNow(),
        b = [Дата рождения2],
        c = Date.Year(a) - Date.Year(b)
      in
        c - Number.From(a < Date.AddYears(b, c))
  ),
  rec = [
    #"до 16"       = {1 .. 15},
    #"от 16 до 24" = {16 .. 23},
    #"от 24 до 35" = {24 .. 34},
    #"от 35 до 50" = {35 .. 49},
    #"от 50"       = {50 .. 969}
  ],
  tbl = Table.ExpandListColumn(Table.RenameColumns(Record.ToTable(rec), {"Value", "Age"}), "Age"),
  join = Table.Join(age, "Age", tbl, "Age"),
  sort = Table.Sort(join, {"ID", 0})
in
  sort
Изменено: Aleksei_Zhigulin - 24.01.2021 01:57:11
Удаление строк из таблицы если сумма по строке 0 в Power Query
 
PooHkrd, понял, значит, это фича  :) Спасибо.
Удаление строк из таблицы если сумма по строке 0 в Power Query
 
Добрый вечер!

PooHkrd, вот эта часть, вроде как, лишняя:
Код
&{0}
PowerPivot, PowerQuery, PowerBI
 
Оставлю тут ссылку на подкаст товарища Ларса Шрайбера (да, теперь про Power BI можно ещё и слушать :) ): https://ssbi-blog.de/podcast/
Сделать сводную по годам в PQ
 
NV.Telegin, формулы у Вас ровно такие же, русские - названия шагов, на работу запроса это не влияет.
DAX Power BI Ошибка "Недостаточно памяти для завершения операции" при вставке формулы столбца
 
Цитата
PooHkrd написал:
Я в книжку (Гайд ту дакс) от итальянцев только вот начал вгрызаться
Цитата
PooHkrd написал:
Использовать EARLIER чтобы выглянуть за пределы текущего контекста, или тупо запомнить его в переменную и её уже использовать уже в CALCULATE?
Вопрос по свежепрочитанному, так что влезу с ответом :)  Итальянцы пишут, что с появлением переменных необходимость в EARLIER отпала:

Цитата
There are no further reasons to use EARLIER in newer DAX code because variables are a better way to save the required value when the right row context is accessible. Using variables for this purpose is a best practise and results in more readable code.
Т.е. насчёт производительности можно сделать вывод, что она у переменных точно не хуже EARLIER.
Изменено: Aleksei_Zhigulin - 22.08.2020 12:53:11
PQ.Автоматическое добавление новых столбцов в расчет
 
Цитата
Student64 написал:
хотелось что то побыстрее pivot / unpivot
Если скорость - проблема, тогда, как и предложил PooHkrd, лучше уж в Power Pivot.
PQ.Автоматическое добавление новых столбцов в расчет
 
Student64, по старинке:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    unpivot = Table.UnpivotOtherColumns(Source, {"Клиент"}, "Атрибут", "Сумма"),
    split = Table.SplitColumn(unpivot, "Атрибут", (x)=>let a = if Text.StartsWith(x,"Д") then "Доход" else "Расход" in {a, Text.Split(x,a){1}}, {"Тип", "Продукт"}),
    pivot = Table.Pivot(split, List.Distinct(split[Тип]), "Тип", "Сумма"),
    replace = Table.ReplaceValue(pivot,null,0,Replacer.ReplaceValue,{"Доход","Расход"}),
    add = Table.AddColumn(replace, "Доход - Расход", each List.Sum({[Доход],[Расход]})),
    add1 = Table.AddColumn(add, "Расход/Доход", each [Расход]/[Доход]),
    unpivot1 = Table.UnpivotOtherColumns(add1, {"Клиент", "Продукт"}, "Атрибут", "Сумма"),
    merge = Table.CombineColumns(unpivot1,{"Атрибут","Продукт"},Text.Combine,"temp"),
    pivot1 = Table.Pivot(merge, List.Distinct(merge[temp]), "temp", "Сумма")
in
    pivot1
Порядок столбцов другой, но, возможно, так даже сподручней.
Развернуть один столбец в несколько в PQ, Речь именно про Power Query
 
Light-XP, примерно так:
Код
let
    Source = Record.ToTable([name1={[a="aa1",c="cc1",z="zz1"],[a="aa2",c="cc2",z="zz2"],[a="aa3",c="cc3",z="zz3"]},
                             name2={[a="aa3",c="cc3",z="zz3"]},
                             name3={[a="aa1",c="cc1",z="zz1"],[a="aa3",c="cc3",z="zz3"]}]),
    n=1, //заранее известный порядковый номер искомого поля в записи (нумерация с нуля)
    transform = Table.TransformColumns(Source, {"Value", each Table.Transpose(Table.FromRecords(_)){n}}),
    expand = Table.ExpandRecordColumn(transform, "Value", List.Union(List.Transform(transform[Value], Record.FieldNames)))
in
    expand
импорт только отфильтрованных строк таблицы в запросе Powre Query
 
Shikamaru, вот, уже лучше  :)  По сути, Вы говорите о параметрах запроса. Один из способов реализации - таблица параметров. Почитать можно, например, здесь. Сайт почему-то сейчас не работает, потому ссылка на кэш Google.
импорт только отфильтрованных строк таблицы в запросе Powre Query
 
Shikamaru, ну ИМХО, получать разные результаты в запросе, если кто-то добавил в таблице фильтр, было бы куда менее приятно. Но вкусы у людей разные, это я понимаю :)  
Страницы: 1 2 3 4 5 6 7 8 9 След.
Наверх