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

Страницы: 1 2 3 4 5 6 7 8 9 След.
Как сделать в 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, ну ИМХО, получать разные результаты в запросе, если кто-то добавил в таблице фильтр, было бы куда менее приятно. Но вкусы у людей разные, это я понимаю :)  
импорт только отфильтрованных строк таблицы в запросе Powre Query
 
Shikamaru, так ведь срез не для того предназначается, чтобы ограничивать получаемые PQ данные. Да и если Вы просто сделаете фильтр в таблице, в PQ подтянутся все данные, не только отфильтрованные. И это хорошо.
Расчет разницы между датами из одной колонки в Power Query, Описание приемов и способов поиска минимальной и максимальной даты с последующим определением разницы в месяцах
 
Цитата
PooHkrd написал:
итератор в AddColumns как-то иначе работает чем в остальных функциях
Да, по-моему, на форуме уже поднималась эта тема, потому и запомнилось. Спасибо!
Расчет разницы между датами из одной колонки в Power Query, Описание приемов и способов поиска минимальной и максимальной даты с последующим определением разницы в месяцах
 
PooHkrd, моя вторая конструкция с вопросом провисела всего пару минут, но Вы таки успели на него ответить  :D

Возможно, Вы правы, и List.Buffer действительно ускоряет быстродействие. Но в мою картину мира это пока не укладывается  :) .  Прогнал на миллионе строк - оба запроса обновляются одинаковое время (~ 20 сек.). Правда, проверял на числах, а не датах.
Код
= Table.AddColumn(types, "aaa", let a = types[Столбец2] in each List.Max(a) - List.Min(a))
Код
= Table.AddColumn(types, "aaa", let a = List.Buffer(types[Столбец2]), b = List.Max(a), c = List.Min(a) in each b - c)

Это наводит на крамольные мысли, что лист a запоминается один раз, а не пересчитывается в каждой строке.
Расчет разницы между датами из одной колонки в Power Query, Описание приемов и способов поиска минимальной и максимальной даты с последующим определением разницы в месяцах
 
PooHkrd, точно, спасибо! Порядок each упустил из виду. Вот эту встроенную конструкцию неправильно вспомнил:
Код
= Table.SelectRows(Source, let earliest = List.Min(Source[Col1]) in each [Col1] = earliest)
Изменено: Aleksei_Zhigulin - 28.05.2020 14:42:01
Расчет разницы между датами из одной колонки в Power Query, Описание приемов и способов поиска минимальной и максимальной даты с последующим определением разницы в месяцах
 
PooHkrd, доброго дня,

Довольно редко сталкиваюсь с необходимостью оптимизировать быстродействие запросов, но разве List.Buffer здесь что-то ускоряет? Мне казалось, что при конструкции let a = list in List.Max(a) - List.Min(a) движок будет обращаться к списку a только один раз, неважно, сколько раз этот список будет встречаться в выражении in.
Страницы: 1 2 3 4 5 6 7 8 9 След.
Наверх