Страницы: 1 2 След.
RSS
Power Query Замена данных в массиве на основе справочника
 
День добрый.
Подскажите как заменить данные в массиве по условию.
В массиве указан план работы помещения, если указан ремонт то количество мест равно 0, если помещение М то количество из справочника, если Ж то ко количество также из того же справочника.

Понимаю что используется функция Table.ReplaceValue, но как ее заставить работать по всему массиву, а не по одному столбцу и по разным данным не могу разобраться..

Спасибо.
 
Доброе время суток.
Вариант.
 
Андрей VG, Спасибо.

Подскажите как понять часть функции в последней строке: T(s, o, n) => dict{[Наименование=s]}?[Количество]?
 
Цитата
mitox написал:
(s, o, n) =>
Функция замены s - текущее значение, о - искомое значение в s, n - подставляемое значение в s вместо о.
Цитата
mitox написал:
dict{[Наименование=s]}?[Количество]?
Аналогична конструкции
Цитата
Excel.CurrentWorkbook(){[Name="График"]}[Content]
только добавлены ?, которые обозначают, что если не найдено, то вернуть null
 
Андрей VG, Спасибо.
Жаль что я сам не догадался про функцию (на основе "=>")
 
вариант кнопками
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="График"]}[Content],
    unpivot = Table.UnpivotOtherColumns(Источник, {"Этаж"}, "Атрибут", "Значение"),
    merge = Table.NestedJoin(unpivot,{"Значение"},Справочник,{"Наименование"},"Справочник",JoinKind.LeftOuter),
    expand = Table.ExpandTableColumn(merge, "Справочник", {"Количество"}, {"Количество"}),
    delete = Table.RemoveColumns(expand,{"Значение"}),
    out = Table.Pivot(delete, List.Distinct(delete[Атрибут]), "Атрибут", "Количество")
in
    out
 
Stics, спасибо. И тут я понял что я туплю))) Но если честно искал именно вариант указанный перед этим...
 
Ночи доброй.
В продолжении темы. У меня есть задача которую хочется решить красиво, а не с помощью кнопок - и также стоит задача замены на основе справочников, но только уже не одного, а двух.

Если первый "Справочник" я могу прицепить к "Графику" через слияние запросов (Table.NestedJoin), то вот заменить значения в итогах слияния на основе данных второго "Справочник1" уже не получается даже с подсказкой выше (первый который без использования кнопок).

Если нужно создать новую тему для данного вопроса то готов, просто не хотелось терять логики в рассуждениях.
Изменено: mitox - 23.03.2020 21:34:01
 
А что не позволило вам приложить на листе Итог то что должно получиться? М и Ж - это обозначает сложение чисел из "Справочник1" или просто сцепку двух чисел в виде текста?
 
Андрей VG, Наверное спешка помешала и попытка попробовать использовать вашу подсказку (но уперся в то что кнопками ее не смогу докрутить).
"М и Ж" - означает сложение чисел.из "Справочник1"

Обновил: Андрей VG,  Добавил таблицу с желаемым итогом.
Изменено: mitox - 24.03.2020 14:17:24
 
Не уловил, каким образом должны размножится на недели. Просто соединил.
 
Андрей VG, Спасибо. Но это все же предложение с кнопками.

В решаемой задаче против каждого дня недели у меня стоит дата (я не стал их указывать в примере и ограничился только днями недели) - хотел понять как можно по двум условиям произвести замену значения (возможно с применением Table.ReplaceValue). Предложение с кнопками тоже поможет разобраться с отдельными особенностями PQ.
 
Андрей VG, Еще раз посмотрел решение и понял что в нем происходит трансформация справочника а не замена данных в Графике. Вспомнил Ваш вопрос и понял что на сколько понятно я задал вопрос такое решение и получил.

Попробую еще раз - у меня есть календарь в котором отдельные дни недели меняют свой режим работы на Предприятиях так как есть праздники, переносы и т.д. Во вложенном файле к Примеру я добавил даты (февраль 2020 - когда 24 февраля был выходной и предприятия работали, тогда как в остальные понедельники это выходной).

Так что нужно решение с заменой данных в Графике а не трансформация справочника. Еще раз Спасибо за терпение.
 
mitox, если не будет других вариантов, то можно так
 
mitox, а чем вас собственно "кнопочные" решения с Table.Join  не устраивают? Да, может Table.ReplaceValue выгладит несколько привлекательнее, но, не давно в обсуждениях было сопоставление Table.ReplaceValue и цепочки Table.AddColumn, Table.RemoveColumns, Table.RenameColumns для целей замены значений столбца - Table.ReplaceValue - оказался гораздо медленнее. Так что не всегда кнопочные решения хуже.
 
Андрей VG, Не имею ни чего личного))) против кнопочных решений. Просто хочется понять как работает вариант с заменой значений (Table.ReplaceValue).

Хотя нет Андрей VG, имею я против кнопочных вариантов - они уникальны и требуют доработки под изменяющиеся условия. Посмотрел два варианта доработок - и оба работают со справочником, который потом пристегивают к графику.

Мне же нужно заполнить график по первому справочнику, а потом произвести замену значений в нем по второму справочнику. Такой алгоритм более универсальный. К примеру в мое рабочем варианте даты указаны за  период с 1 января 18 года по 31 декабря 20 года - и как понимаете, праздничные дни, смещения из за них - очень сильно корректируют выходные дни.
Изменено: mitox - 24.03.2020 12:02:48
 
Михаил Л, В любом случае спасибо - сижу разбираю каждый пример. Все равно все нужно переделывать под мой рабочий вариант. Так интересней - понимать как работает система.

Вариант рабочий, но если вдруг четверг окажется выходным то придется или справочник расширять или что то еще делать)))
Изменено: mitox - 25.03.2020 08:00:56
 
до кучи
Код
let
    Source = 
        Excel.CurrentWorkbook(){[Name="Справочник"]}[Content],
    Dictionary = 
        Table.RenameColumns(Table.TransformColumns(
            Source,
            let 
                a = Excel.CurrentWorkbook(){[Name="Справочник2"]}[Content], 
                b = Record.FromList(Table.ToRecords(Table.RemoveColumns(a,{"Наименование"})),a[Наименование]), 
                c = List.Skip(Table.ColumnNames(Source),2) 
            in 
                List.Transform(
                    c,
                    (s)=>
                    { s,
                      each try 
                          Expression.Evaluate("#"""&Text.Replace(Text.From(_)," и ","""["&s&"]+#""")&"""["&s&"]",b)
                      otherwise 
                          null
                    }
                )
        ),{{"Наименование","a"},{"Тип","b"}}),
    Schedule = 
        Excel.CurrentWorkbook(){[Name="График"]}[Content][[Дата],[Наименование],[Тип]],
    Joined = 
        Table.Join(Schedule,{"Наименование","Тип"},Dictionary,{"a","b"},JoinKind.LeftOuter),
    Result = 
        Table.TransformColumnTypes(Table.RemoveColumns(Joined,{"a", "b"}),{{"Дата", type date}})
in
    Result
 
Андрей Лящук, Спасибо.
Самое Лаконичное, но и самое непонятное для меня в части Dictionary (со строки 4 по 22-ую). По рознь понимаю функции а слитно не могу увязать
 
Можно еще как-то так. Не очень я люблю замены:
Код
let
    Спр2 = Excel.CurrentWorkbook(){[Name="Справочник2"]}[Content],
    РазворотСпр2 = Table.UnpivotOtherColumns(Спр2, {"Наименование"}, "Атрибут", "Значение"),
    ПолныйСпр2 = РазворотСпр2 & Table.Group(РазворотСпр2, {"Атрибут"}, {{"Значение", each List.Sum([Значение])}, {"Наименование", each "М и Ж"}}),
    РазворотСпр = Table.UnpivotOtherColumns( Excel.CurrentWorkbook(){[Name="Справочник"]}[Content], {"Наименование", "Тип"}, "Атрибут", "Значение"),
    Джойн = Table.NestedJoin(РазворотСпр,{"Атрибут", "Значение"},ПолныйСпр2,{"Атрибут", "Наименование"},"Спр2",JoinKind.LeftOuter),
    ПолныйСпр = Table.ReplaceValue( Table.ExpandTableColumn(Джойн, "Спр2", {"Значение"}, {"Спр2.Значение"}), null , 0, Replacer.ReplaceValue, {"Спр2.Значение"} ),
    График = Table.TransformColumnTypes( Excel.CurrentWorkbook(){[Name="График"]}[Content][[Дата],[Наименование],[Тип]], {{"Дата", type date}, {"Наименование", type text}, {"Тип", type text}} ),
    ДжойнФинал = Table.NestedJoin( График, {"Наименование", "Тип"}, ПолныйСпр, {"Наименование", "Тип"}, "График", JoinKind.LeftOuter ),
    ВыводимНужное = Table.Sort( Table.ExpandTableColumn(ДжойнФинал, "График", {"Атрибут", "Спр2.Значение"}), {{"Атрибут", Order.Ascending}} ),
    ВотЭтоПоворот = Table.Pivot(ВыводимНужное, List.Distinct(ВыводимНужное[Атрибут]), "Атрибут", "Спр2.Значение")
in
    ВотЭтоПоворот
Вот горшок пустой, он предмет простой...
 
PooHkrd, Спасибо. С каждым вариантом новые алгоритмы. Особенно порадовался строчке 4 - не знал что так можно соединять и добавлять колонки. Спасибо.

Андрей VG, Соглашусь с вами. Просто я еще не умею готовить кошек ))
 
Цитата
mitox написал:
вариант тоже не рабочий, если вдруг четверг окажется выходным то не сработает - или справочник расширять (что не комильфо) или что то еще делать)))
У вас уже есть решение, которое при выходном четверге без расширения справочника и не делая что то еще))) выдаст нужный вам результат?
 
Михаил Л,  Свою задачу я решил использовав отдельные элементы предложенных решений, но не как хотел - через замену. Наверное пока и не нужно... Еще раз спасибо.
 
Хотел увидеть в примере с нерабочим четвергом чем мой вариант решения тоже не рабочий
Цитата
mitox написал:
задачу я решил использовав отдельные элементы предложенных решений
И на это решение посмотрел бы)))
 
Михаил Л, Я уже писал - все предложенные варианты идут от корректировки справочника (ваш в том числе) в котором указаны дни недели.

Так как у меня период гораздо шире (с 1 января 18 года по 31 декабря 20 года) чем в Примере (17 февраля 20 по 19 марта 20), а также учитывает переносы выходных и праздников при определении типа дня (будни/выходной), то мне пришлось расширить свой справочник: у каждого дня недели - появилась быть возможным и выходным и будним днем.

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

Михаил Л, Смотрите ))))))))))))))))
 
Цитата
mitox написал:
то мне пришлось расширить свой справочник: у каждого дня недели - появилась быть возможным и выходным и будним днем
Исходя из этого следующее сообщение
Цитата
mitox написал:
Но вариант тоже не рабочий, если вдруг четверг окажется выходным то не сработает - или справочник расширять (что не комильфо) или что то еще делать)))
можно было бы подкорректировать, например, так:
Код
Вариант рабочий, если вдруг четверг окажется выходным то или справочник расширять или что то еще делать)))
Я бы и мог на все это забить, но мне тяжело все это перечитывать
 
Михаил Л, Поправил ))
 
mitox, спасибо
 
Андрей VG,  Был самоуверен в понимании ответа на свой вопрос
Цитата
mitox написал:
Подскажите как понять часть функции в последней строке: T(s, o, n) => dict{[Наименование=s]}?[Количество]?

Пытаюсь еще раз разобраться
Код
result  = Table.ReplaceValue(graphs, null, null, (s, o, n) => dict{[Наименование=s]}?[Количество]?, replaceInCols)
О функции Table.ReplaceValue - почитал, понимаю что работает как "где что на что поменять".
"graphs" - указывает на строку в данных которой нужно произвести замену
", null, null, (s, o, n)" - видимо нужно читать слитно, где ", null, null, " заменяет "(s, o, n)" . Это получается как вложенная функция?

Самое сложное начинается тут "=> dict{[Наименование=s]}?[Количество]?, replaceInCols":
-  dict{[Наименование=s]} - задается значение s для колонки  в таблице и одновременно содержание колонки как о
- [Количество] - задается значение n
- replaceInCols - вот эту часть как результат понимаю, но выразить словами не могу...

Помогите разобраться пожалуйста.
 
Цитата
mitox написал:
dict{[Наименование=s]}?[Количество]?, replaceInCols"
replaceInCols передает список названий столбцов, в которых будет работать формула
В отдельном шаге вставьте
Код
= dict{[Наименование="Ж"]}?[Количество]?
Посмотрите что будет если поменять Ж на М или Я
вот еще ссылка
Страницы: 1 2 След.
Наверх