Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 140 След.
Power Query: поиск дубликатов по определенным столбцам таблицы
 
Михаил Л, Вы уверены, что на таблице с 100500 строк выполнять 100500 джойнов к небуфферизированному предыдущему шагу - хорошая идея? или даже к буфферизированному...
F1 творит чудеса
Power Query: поиск дубликатов по определенным столбцам таблицы
 
CainV, вот вам без упоминания столбцов В1 и т.п.
Код
let
    Table3 = Table.Distinct(Table1 & Table2),
    #"Added Index" = Table.AddIndexColumn(Table3, "Index", 0, 1),
    Grouped = Table.Group(#"Added Index", {"A1", "A2", "A3", "A4", "A5", "A6"}, {{"idx", each _[Index], type list}}),
    Merged = Table.Join(#"Added Index", {"A1", "A2", "A3", "A4", "A5", "A6"}, Grouped, {"A1", "A2", "A3", "A4", "A5", "A6"}),
    #"Added Custom" = Table.AddColumn(Merged, "Duplicated", each Text.Combine(List.Transform(List.Difference([idx], {[Index]}), Text.From),","), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"idx"})
in
    #"Removed Columns"
F1 творит чудеса
PQ: Индекс сортировки внутри одной категории
 
Михаил Л, та самая таблица _ внутри шага Group может оказаться отсортированной в другом порядке, еще до индексирования. Хотя тут сложно сказать точно, поэтому я и говорю "на всякий случай".
F1 творит чудеса
PQ: Индекс сортировки внутри одной категории
 
buchlotnik, я бы на всякий случай добавил временную индексацию перед группировкой тоже. При группировке порядок строк не гарантирован
F1 творит чудеса
PQ. Полное внешнее соединение по группам
 
Цитата
OblivionR написал:
вот я решил таким способом устранить проблему,
это очень плохая идея
Вот у вас есть такая модель:
Данные1 <- Справочник -> Данные2
В справочнике все артикулы, которые есть в Данные1 и Данные2, или даже больше.
Если вы берете для аналитики столбец артикула из справочника, у вас ничего ниоткуда не выпадет. А если будете брать артикулы из одной из таблиц Данные, то конечно это приводит к выпаданию непересекающихся артикулов, но кратно увеличивать количество строк в таблицах Данных для того, чтобы там были все артикулы - это ОЧЕНЬ плохая идея.
F1 творит чудеса
PQ. Полное внешнее соединение по группам
 
Мой код накликан на предыдущей версии файла, но поясните все же, что у вас внутри таблицы происходит?
Столбец Да/Нет как должен заполняться для добавляемых строк - потому что сценарий, или потому что дата, или почему в одном случае 0, в другом стоит 1?
В столбце план должен стоять 0?
F1 творит чудеса
PQ. Полное внешнее соединение по группам
 
.
Изменено: Максим Зеленский - 30 Мар 2020 11:22:19 (замена исходного файла)
F1 творит чудеса
Консолидация Таблиц через Power Query
 
Как должен выглядеть конечный результат?
Если правильно понял задачу, просто удалите в первой таблице наполнитель и добавьте вторую таблицу ниже
F1 творит чудеса
Как выгрузить из огромного файла HTML данные в Power Qwery
 
А вообще если не справляется PQ, попробуйте EasyMorph, например, преобразовать эту html в csv, я думаю, для него не проблема, он побыстрее будет
F1 творит чудеса
Как выгрузить из огромного файла HTML данные в Power Qwery
 
У меня прочиталось, хоть и долго читалось. Думаю, долгое чтение в основном из-за того, что в таблице некоторые ячейки имеют довольно большой ROWSPAN, и PQ преобразовывает его в плоский вид, заполняя строки для таких ячеек.
Так что совет - бейте на маленькие файлы - по месяцу, или еще мельче - насколько это будет съедаемо PQ/

Специально перевернул таблицу - ничего в конце не съедается:
F1 творит чудеса
Найти крайнее значение в строке и вывести название заголовка в PQ
 
PooHkrd, такой код не вернет 0 там, где все столбцы КОЛ РЕВ пустые (как показано в примере), в отличие от кода Андрей Лящук. (кстати, надо все же 5 столбцов убирать).
Мои пару вариантов тоже на всякий случай:
Код
=Table.FirstValue(Table.ReverseRows(Table.SelectRows(Table.Skip(Record.ToTable(_),5),each [Value]<>null)), 0)

Код
=try Table.Last(Table.SelectRows(Table.Skip(Record.ToTable(_),5),each [Value]<>null))[Name] otherwise 0
F1 творит чудеса
Как выгрузить из огромного файла HTML данные в Power Qwery
 
Цитата
mitox написал:
данных (пустые ячейки).
если в выгруженных файлах всегда одно и то же количество столбцов в таблицах, и "съедаются" только пустые ячейки слева (но не посередине), то, может быть, выровнять данные по правому краю? Сымитировав таким образом пустые ячейки.
Ну вообще даже без самого маленького образца данных сложно дать совет - слишком широк может быть спектр проблем и решений
Оффтоп
F1 творит чудеса
Как выгрузить из огромного файла HTML данные в Power Qwery
 
странно. возможно, там формат html, который чем-то не нравится PQ. А просто Excel эти файлы открываются? попробуйте, может быть, их получится пересохранить
F1 творит чудеса
Как выгрузить из огромного файла HTML данные в Power Qwery
 
насколько огромный файл-то?
F1 творит чудеса
Подсчет количества затраченного времени в DAX
 
если правильно понял, то нужно вот это:
Код
=
SUMX (
    VALUES ( 'Таблица1'[Номер трака] );
    CALCULATE (
        MAX ( 'Таблица1'[Окончание разгрузки] ) - MIN ( 'Таблица1'[Начало разгрузки] )
    )
)
формат меры потом сделать [ч]:мм
Если нужно именно в часах как целое число и и доля от часов после запятой, надо добавить умножение на 24
F1 творит чудеса
ячейки с зада :), и такое бывает
 
 
F1 творит чудеса
Преобразовать таблицу. Перенести атрибуты из строк в столцы
 
Подожду еще, условия все время меняются.
В первом сообщении было совсем другое.
Вопросы:
  1. Всегда второй столбец должен переезжать в Attr. Group 1, Attr. Group 2 и так далее?
  2. Первых столбцов всегда 3?
F1 творит чудеса
Дублирование данных при объединении запросов в Power Query
 
я чего-то не понимаю. У вас на один номер счета и дату приходится два банковских документа. Из-за того, что в счете две строки, естественно, подтягивается для из банка по две строки для каждой из них. Если у вас суммы должны совпадать, включите их в ключ объединения, но для этого они должны появиться в таблице Банк. Если это по какой-то причине невозможно, можно попробовать перечислять номера банковских документов через запятую.
F1 творит чудеса
DAX. Округление в мере с накоплением десятых долей и распределением их по временной шкале, пытаюсь округлить прогноз с минимальными потерями
 
поясню на всякий случай свою мысль, может, кто-то будет искать по форуму, ну и самому полезно :)
Проблема как обычно многосоставная:
  • Ваша формула в Excel при переносе в DAX подразумевает, что для одной и той же меры необходимо учитывать ее значения, рассчитанные ранее. Но, так как в DAX нет перезаписываемых переменных, в нем почти невозможно реализовать алгоритм Xi=X(i-1)+n, привычный по Excel.
  • Каждое значение в DAX рассчитывается индивидуально, независимо от расчета других значений, и отдельный расчёт не имеет представления о результатах других расчётов.
  • Соответственно, если мы хотим рассчитать что-то как сумму выражений по типу Xi = SUM(X1...X(i-1)), то в этом расчете нам надо повторить расчёт всех предыдущих X.
Исходя из этого есть практически единственный workaround (по крайней мере, мне ничего не пришло в голову другого):
Цитата
Если у нас i известен и не очень большой, то можно создать формулу DAX, которая будет внутри себя создавать VAR X1, VAR X2 и так далее для всех возможных i.
Не знаю, подходит ли для вас такой вариант. Для потенциальных 12 месяцев прогноза можно заморочиться и создать 12 переменных, которые будут рассчитывать и запоминать предыдущее состояние для последующих расчетов. Но если число месяцев прогноза не ограничено, то задача таким способом не решается.

Для ограниченного числа месяцев прогноза (например, 12), решение примерно такое:
Создаем два доп.столбца в календаре:
Код
IsForecast = IF(MAX('Факт'[Дата])<'Календарь'[Date];1;0)
Код
n =
VAR _RankTable =
    CALCULATETABLE (
        VALUES ( 'Календарь'[МММ-ГГГГ] );
        'Календарь'[IsForecast] = 1;
        ALL ( 'Календарь' )
    )
VAR _n =
    RANKX ( _RankTable; 'Календарь'[МММ-ГГГГ]; 'Календарь'[МММ-ГГГГ]; ASC )
RETURN
    IF ( 'Календарь'[IsForecast] = 1; _n )

Далее вот такая мера - немного монстр:
Код
Округленный прогноз = 
VAR __CurrentN = IF(HASONEVALUE('Календарь'[n]);MAX('Календарь'[n]))
VAR __ForecastTable = CALCULATETABLE(VALUES('Календарь'[МММ-ГГГГ]); ALL('Календарь'); 'Календарь'[IsForecast]=1; 'Календарь'[n]<=__CurrentN)
VAR __1 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]=1); 0)
VAR __2 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=2) - __1; 0)
VAR __3 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=3) - __2 - __1; 0)
VAR __4 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=4) - __3 - __2 - __1; 0)
VAR __5 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=5) - __4 - __3 - __2 - __1; 0)
VAR __6 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=6) - __5 - __4 - __3 - __2 - __1; 0)
VAR __7 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=7) - __6 - __5 - __4 - __3 - __2 - __1; 0)
VAR __8 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=8) - __7 - __6 - __5 - __4 - __3 - __2 - __1; 0)
VAR __9 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=9) - __8 - __7 - __6 - __5 - __4 - __3 - __2 - __1; 0)
VAR __10 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=10) - __9 - __8 - __7 - __6 - __5 - __4 - __3 - __2 - __1; 0)
VAR __11 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=11) - __10 - __9 - __8 - __7 - __6 - __5 - __4 - __3 - __2 - __1; 0)
VAR __12 = ROUND(CALCULATE(SUMX(__ForecastTable;[Прогноз]); 'Календарь'[n]<=12) - __11 - __10 - __9 - __8 - __7 - __6 - __5 - __4 - __3 - __2 - __1; 0)
RETURN
SWITCH(__CurrentN; 1;__1; 2;__2; 3;__3; 4;__4; 5;__5; 6;__6; 7;__7; 8;__8; 9;__9; 10;__10; 11;__11; 12;__12)

файлы Excel и PBIX во вложении
F1 творит чудеса
DAX. Округление в мере с накоплением десятых долей и распределением их по временной шкале, пытаюсь округлить прогноз с минимальными потерями
 
задача непростая, ибо рекурсии в DAX нет, а ее имплементации обычно очень узконаправленные и труднопереносимые на другие задачи.
я бы советовал менять алгоритм округления, хотя бы до уровня конечный прогноз = round(сумма прогнозов - сумма предыдущих округленных прогнозов). грубее, но ошибка округления также будет смазываться.
F1 творит чудеса
"невидимый файл" - не могу увидеть открытый файл
 
 
F1 творит чудеса
DAX. Найти дату начала недели.
 
OblivionR, если вам неважно, что первая неделя года, начинающегося не в понедельник, начнется в предыдущем году, то ок, тогда код buchlotnik
F1 творит чудеса
Power Query. Меняю источник данных, выдает ошибку
 
Цитата
vlas20 написал:
Заменяю в Параметрах источника данных.
надо не тут, а в самом коде запроса менять источник данных
F1 творит чудеса
POWER QUERY - Создать функцию которая создает и удаляет столбцы по условию, Создать функцию создающую и удаляющую столбец по условию
 
Цитата
lostandleft написал:
Немного более масштабный, и несколько менее удобый чем:  
краткость кода <> правильность и быстродействие.
Цитата
lostandleft написал:
просто передать условие true false
простите, у вас каша.
Цитата
lostandleft написал:
True False как результат от вычислений (совершенно не обязательно связанных с конкретными столбцами),
Каких вычислений? Не бывает абстрактных "вычислений", то есть вычислений "вообще". Есть конкретные вычисления. Эти конкретные вычисления должны быть где-то прописаны. Сравнение чего-то с чем-то (типа [Новый]=6) тоже вычисление. Но вы не хотите его прописывать - вы хотите передать результат - true/false, но он откуда берется?
Цитата
lostandleft написал:
Цитата
PooHkrd написал:
В разных местах кода требуется один и тот же обработчик - выносим его в функцию чтобы сократить код.
Именно так и есть!!!
Так в том то все и дело, что у вас обработчик не один и тот же, а разный! Как должен выглядеть обработчик неизвестного условия? Сейчас у вас это А-Б=1, затем это Б>5, потом у вас это "Б начинается с А и заканчивается В". Это совершенно разная проверка, разный код обработчика. Серьезно, даже странно это объяснять.

Не бывает универсальных обработчиков, практически. Приведенный мною код неэффективен, он будет долго работать, так как для каждой строки таблицы он будет делать лишнее действие, замедляющее его очень сильно, даже боюсь сравнивать.

Код, который привел Андрей VG в #4, как раз и позволяет сделать так, чтобы в вашу функцию замены столбца передавался тот обработчик, который нужен в конкретном случае. Но сделать быстрый обработчик, который будет еще и угадывать мысли - ну нет.

Разобраться с функциями и конкретно этим кейсом - не сложно. Но для начала вам надо разобраться с работой встроенных функций.

подробное объяснение под катом

F1 творит чудеса
POWER QUERY - Создать функцию которая создает и удаляет столбцы по условию, Создать функцию создающую и удаляющую столбец по условию
 
Андрей VG, бесспорно. Но тут же человек хотел хотелку. Можно? Можно. Нужно? Нет
Нам так и не сказали, в каком виде хотят передать условие.
Чем не устраивает передача безымянной функции сравнения я тоже не понял.
F1 творит чудеса
POWER QUERY - Создать функцию которая создает и удаляет столбцы по условию, Создать функцию создающую и удаляющую столбец по условию
 
В общем, вот вам универсальная функция, и не стоит так эмоционировать :)
Условие задается так же, как если бы вы писали формулу доп.столбца, т.е. имена столбцов в квадратных скобках

Код
// НовыйСтарый2
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    ИзмененТип = Table.TransformColumnTypes(Источник,{{"Старый", Int64.Type}, {"Новый", Int64.Type}}),
    Custom1 = myFn2(ИзмененТип, "Старый", "Новый", "[Новый]-[Старый]=1")
in
    Custom1

// myFn2
(
    TABLE as table, 
    Old as text, 
    New as text, 
    condition as text // "[Column1]-[Column2]>1", "[Column1]-[Column2]=3", "[Column2]=6"
) => 
        Table.RenameColumns(
            Table.RemoveColumns(
                Table.AddColumn(
                    TABLE, 
                    "TMPcol", 
                    each if Expression.Evaluate(condition, #shared & [_=_]) then Record.Field(_, New) else Record.Field(_, Old)), 
                {Old, New}), 
            {{"TMPcol", Old}})
F1 творит чудеса
POWER QUERY - Создать функцию которая создает и удаляет столбцы по условию, Создать функцию создающую и удаляющую столбец по условию
 
Цитата
Андрей VG написал:
задача в тех рамках, которые я ставлю и хотел бы видеть - нерешаемая и точка.Невозможно передать условие, или результат от вычислений, или само вычисление как параметр в функцию, в частности невозможно передать true или falce.
вы не правы
Цитата
lostandleft написал:
Почему я просто не могу передать условие? Я ведь хочу написать функцию, чтобы сократить код а не увеличить его.Нет ли способа все действия сделать в функции а в нее передавать только один маленький параметр с условиями?
Как должен выглядеть параметр с условиями? В каком виде вы хотите задавать условие? Ведь оно у вас, в соотвествии с #7, вообще может быть любое. Вы с этим определитесь, пожалуйста, а то у вас ни число аргументов функции, ни ее условие не определены, но она должна делать то, что она заранее не знает.
F1 творит чудеса
Оптимизация запроса PQ. Новый столбец и удаление старого, или построчная замена по условию?
 
Вот это:
Код
List.First(List.Select({какоетовыражение,[Цена]}, each _ <> null))
аналог функции COALESCE в DAX или SQL, возвращает первое из значений списка, не равных null
F1 творит чудеса
PQ. Функция подмены части строки в одном поле по условиям из другой таблицы
 
Михаил Л, ну зачем так сложно :)
Код
= Table.TransformColumns(
    Source,
    {{"Было", each  Text.Combine(
        List.ReplaceMatchingItems(
            Text.Split(_," "), 
            Table.ToRows(ReplacePairs)
            ),
        " ")
        }}
    )
Изменено: Максим Зеленский - 20 Мар 2020 11:10:16
F1 творит чудеса
Оптимизация запроса PQ. Новый столбец и удаление старого, или построчная замена по условию?
 
Цитата
lostandleft написал:
благодарность за перевод
спасибо, Goggle Translate!  :D
Цитата
lostandleft написал:
при объявлении каждой новой переменной, по ощущениям PQ выполняет весь запрос заново.
это только в редакторе при разработке - ему нужно сгенерировать для вас предпросмотр состояния запроса на конкретный шаг и закэшировать его. При рабочем выполнении запроса ему плевать на предпросмотр, поэтому работает совсем не так.
Вообще любой сколь угодно сложный код PQ можно записать вообще в одну строку, и никакого влияния на производительность запроса это не даст.

Возвращаясь к вопросу оптимизации - первый вариант быстрее. Но прийти к пониманию этого можно аж двумя способами:

1) заранее зная, какой механизм заложен в ту или иную функцию PQ и что она делает под ковром, и как работает внутренний оптимизатор PQ
2) методом проб и ошибок, тестирования, изучения документации, задавания вопросов, чтения форумов и блогов

Обычно наш метод второй (если кто-то из разработчиков не поделился сокровенным знанием).
Те, кто пишет более-менее оптимальный код - миксуют оба метода.
Так что все нормально, вопрошайте, только следите за корректностью примеров, и вам весьма воздастся.

Дополнительная оптимизация вам - постарайтесь понять, что и зачем сделано:
Код
let
    table2renamed = Table.RenameColumns(Таблица2, {{"Груз", "Gruz"}}),
    table2tiny = Table.SelectColumns(table2renamed, {"Gruz","Цена"}),
    Источник = Table.Join(Таблица1, {"Груз"}, table2tiny, {"Gruz"}, JoinKind.LeftOuter),
    AddNewColumn = Table.AddColumn(Источник, "CorrectedPrice", each List.First(List.Select({какоетовыражение,[Цена]}, each _ <> null))),
    Removed = Table.RemoveColumns(AddNewColumn , {"Gruz", "Цена"}),
    final = Table.RenameColumns(Removed, {{"CorrectedPrice", "Цена"}}),
in
    final

а вот то же самое, записанное в одну строку:
Код
 = Table.RenameColumns(
     Table.RemoveColumns(
         Table.AddColumn(
             Table.Join(
                 Таблица1, {"Груз"}, 
                 Table.RenameColumns(Таблица2, {{"Груз", "Gruz"}})[[Gruz],[Цена]], {"Gruz"},
                 JoinKind.LeftOuter), 
            "CorrectedPrice", 
            each List.First(
                List.Select(
                    {какоетовыражение,[Цена]}, each _ <> null)
                    )
                ),
        {"Gruz", "Цена"}),
    {{"CorrectedPrice", "Цена"}}
    )

Как по мне, так первый код понятнее (хотя он на 100% соответствует второму).

дальнейшая оптимизация зависит больше от какоетовыражение, и это выражение может в корне поменять код.
F1 творит чудеса
Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 140 След.
Наверх