Страницы: 1
RSS
Power query. Как устранить ошибку при преобразовании текстовых значений в тип duration.
 
Здравствуйте!
Прошу подсказать как решить задачу с ошибкой.

Дана таблица с 3-мя столбцами.
Столбец 1 = №
Столбце 2 = Время снимка, где данные указаны в формате:
  • 08:00
  • 32:00
  • 153:00
Т.е. в минутах.

Необходимо преобразовать значение столбца 2 в "Продолжительность" без ошибок и с сохранением всех данных.
Чаще всего, в корне ошибки находятся значения:
  • 32:00
  • 24:00
Я пробовал усечь, заменить пробел на пустоту, но ошибка остаётся.

Прикладываю исходник, где:
  • лист "1" - исходник;
  • лист "2" - выгрузка из PQ
Спасибо!
Изменено: ivanka - 01.04.2020 16:23:04
 
Если это у вас минуты, то вы это в виде минут и скармливайте PQ. Он чуйствительный ко всяким двусмысленностям, и в результате выдает всякую фигню. Хотите понятный результат кормите его понятными данными.  ;)
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([#"№"] <> null and [#"№"] <> "№")),
    #"Добавленный префикс" = Table.TransformColumns(#"Строки с примененным фильтром", {{"Время снимка  (план)", each "00:" & _, type text}}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Добавленный префикс",{{"Время снимка  (план)", type duration}})
in
    #"Измененный тип"
Вот горшок пустой, он предмет простой...
 
PooHkrd, а что вы сделали? Добавили "00:" как данные для "часы"? Все остальные строки нормально же прошли. Хотелось бы понимать почему так произошло.  
Изменено: ivanka - 01.04.2020 16:39:40
 
Цитата
ivanka написал:
Все остальные строки нормально же прошли.
С чего вы взяли? Он их вообще-то распознал как часы, а не минуты, и ошибки выдвал на всех значениях, которые для него были больше чем 23, т.к. 24 часа в сутках.
Вот горшок пустой, он предмет простой...
 
PooHkrd, всё, понял! Благодарю!
Можно ли одной строкой кода ему сразу сказать, что это минуты, чтобы он не думал, что это часы? И тогда не нужно будет подставлять нули.  
 
Цитата
ivanka написал: Можно ли одной строкой кода ему сразу сказать, что это минуты
Так я одной и сказал!  :D  Разве нет? Не хотите плодить строки -учитесь вкладывать шаги друг в друга, это не больно.
Вот горшок пустой, он предмет простой...
 
PooHkrd, если в столбце минуты указаны как "126:42", то при добавлении префикса мы получаем: 00:126:42. Когда формат данных указываем как  "продолжительность", то получается ошибка.
Изменено: ivanka - 01.04.2020 17:57:37
 
Хотели в одну строку, получите, распишитесь:  :D
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([#"№"] <> null and [#"№"] <> "№")),
    #"Добавленный префикс" = Table.TransformColumns(#"Строки с примененным фильтром", List.Transform( {"Время снимка  (план)", "Время снимка  (факт)"}, each {_, (x)=> let t = List.Transform(Text.Split(x, ":"),Number.From) in #duration(Number.IntegerDivide(t{0}, 60 * 24), Number.IntegerDivide(t{0}, 60), Number.Mod(t{0}, 60), t{1}), type duration} ) )
in
    #"Добавленный префикс"
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Хотели в одну строку, получите, распишитесь:
вот жесть) теперь же это всё надо на составляющие разобрать) это займёт часа 4
 
Доброе время суток
Цитата
PooHkrd написал:
Хотели в одну строку, получите, распишитесь:
Алексей, а почему не дотянули действительно до одной строки?
ivanka, по щучьему веленью, по вашему хотенью :D
Код
Table.TransformColumns(Table.SelectRows(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], each ([#"№"] <> null and [#"№"] <> "№")), List.Transform( {"Время снимка  (план)", "Время снимка  (факт)"}, each {_, (x)=> let t = List.Transform(Text.Split(x, ":"),Number.From) in #duration(Number.IntegerDivide(t{0}, 60 * 24), Number.IntegerDivide(t{0}, 60), Number.Mod(t{0}, 60), t{1}), type duration} ) )
 
Андрей VG, просто я очень ленивый.
Вот горшок пустой, он предмет простой...
 
Чего вы такие длинные формулы пишете :)
при переводе в duration можно указать больше 60 минут:
Код
in #duration(0, 0, t{0}, t{1}), type duration}
F1 творит чудеса
 
Максим Зеленский, вот это поворот!
Вот горшок пустой, он предмет простой...
 
PooHkrd, Андрей VG, Максим Зеленский, прошу вас подсказать как работает код. Я декомпозировал его для улучшения читабельности:




итого у меня вопросы к:
Код
each {_, (x)=> let t 
- т.е. возьми столбцы {"Время снимка  (план)", "Время снимка  (факт)"} как "х", и принимай как "t"? Т.е. я могу обращаться к этим двум столбцам через 2 переменные х и t ?

Код
Text.Split(x, ":")
- т.е. возьми столбцы {"Время снимка  (план)", "Время снимка  (факт)"} и раздели текст в них по разделителю ":"

Код
Number.From)
- не понимаю почему мы ничего не пишем внутри этой функции, для меня это как указать функцию ЕСЛИ() без аргументов внутри.

Код
in #duration(0, 0, t{0}, t{1}), type duration}))
- почему для секунд  t{1}, а не t{0}?
 
PooHkrd, Андрей VG, подскажите пожалуйста как обойти ошибку. Ошибка появляется из-за того, что в исходной таблице есть пустые ячейки.

Прикладываю файл "пример" с подключением "Ошибки в Решение 2 (короткое)".
Спасибо!  
Изменено: ivanka - 02.04.2020 12:20:00
 
ivanka, ну вот взяли, и одну строку развалили на много строк, ну как так!  :)
Функция имеет 2 аргумента, собственно таблица которую меняем и второй аргумент это список списков столбцов и тех мутаций, которые мы к ним применяем.
Код
Table.TransformColumns( таблица, {{"столбец1", функция1, тип столбца1 на выходе}, {"столбец2", функция2, тип столбца2 на выходе}} )

В вашем случае в списках функция1/2 и тип 1/2 совпадают, т.е. если функцию объявлять не анонимно, а отдельно, то выглядеть будет так:
Код
фн = (x)=> let t = List.Transform(Text.Split(x, ":"),Number.From) in #duration( 0, 0, t{0}, t{1} )
#"Добавленный префикс" = Table.TransformColumns( 
    #"Строки с примененным фильтром", 
    {{"Время снимка  (план)", фн, type duration}, {"Время снимка  (факт)", фн, type duration}} )

Мне же было лениво и чтобы объявленную функцию не писать 2 раза, я сделал конструкцию преобразования списка столбцов в список списков столбцов и операций над ними, т.е.
Код
List.Transform( {"Время снимка  (план)", "Время снимка  (факт)"}, each{_, фн, type duration} )

на выходе создает именно такой список:
Код
{{"Время снимка  (план)", фн, type duration}, {"Время снимка  (факт)", фн, type duration}}

Про Number.From без аргументов здесь уже неоднократно писалось, что функция это в том числе еще и тип данных и в данном конкретном примере записи:
Код
each Number.From(_)
(x)=>Number.From(x)
Number.From

они равнозначные, тут главное - это четко понимать какое количество аргументов вы передаете в функцию, и какое количество она может принимать, если все совпадает, то работать будет одинаково. Лично мне раньше было понятнее использовать запись с each _. А сейчас вот руку набил, и пишу как короче.
Цитата
ivanka написал:
как обойти ошибку
Добавьте в функцию проверку на пустые значения типа такого:
Код
(x)=> if x = null then null else let t = List.Transform(Text.Split(x, ":"),Number.From) in #duration( 0, 0, t{0}, t{1} )
Изменено: PooHkrd - 02.04.2020 12:41:12 (добавил про обход ошибки)
Вот горшок пустой, он предмет простой...
 
Здесь скорее PooHkrd ушел на уровень нирваны :)
Суть в следующем.
Когда мы делаем Table.TransformColumns, у нее два главных аргумента - это имя таблицы и операция преобразования в виде {имя столбца, функция преобразования, тип результата}
Если мы одновременно преобразовываем несколько столбцов, там будет уже такой вид, как список списков: {{имя 1, функция 1, тип результата 2}, {имя 2, функция 2, тип результата 2}}
Если функция одинаковая, то вот этот список списков (имя-функция-тип) можно генерировать из списка названий столбцов
соответственно вот этот кусочек
Код
        List.Transform(
            {"Время снимка  (план)", "Время снимка  (факт)"},
            each 
                {
                    _, 
                    (x)=> 
                        let 
                            t = List.Transform(Text.Split(x, ":"),Number.From) 
                        in 
                            #duration(0, 0, t{0}, t{1}),
                    type duration
                }
            )

из списка имен {"Время снимка  (план)", "Время снимка  (факт)"} создает список вида
Код
{
    {"Время снимка  (план)", функция, тип},
    {"Время снимка  (факт)", функция, тип}
}

Это преобразование делается путем замены (List.Transform) в исходном списке имен столбцов конкретного имени столбца на список {имя, функция, тип}
Код
each {_, (x)=>тело функции, тип результата} 

означает: для каждого элемента _ исходного списка преобразуй его в вот такой список из самого элемента, функции и типа.

Эта пресловутая функция потом применяется при преобразовании столбца. Де-факто, это такой же List.Transform:
Код
(x)=> 
    let 
        t = List.Transform(Text.Split(x, ":"),Number.From) 
    in 
        #duration(0, 0, t{0}, t{1})

означает: для каждого элемента списка (значения столбца):
  • сделай разбиение по ":"
  • преобразуй каждый кусочек в число
  • запомни получившийся список из 2 элементов как t
  • получи duration как 0 дней, 0 часов, t{0} минут, t{1} секунд
вообще конструкция let тут не обязательна, но она немного ускоряет выполнение.
Все то же самое можно было записать как
Код
(x)=>#duration(0, 0, List.Transform(Text.Split(x, ":"),Number.From){0},List.Transform(Text.Split(x, ":"),Number.From)t{1})

но зачем дважды делить и преобразовывать, если это можно сделать один раз?
Цитата
ivanka написал:
- почему для секунд  t{1}, а не t{0}?
минуты как t{0} - потому что в списке элементы нумеруются от 0.
Цитата
ivanka написал:
- не понимаю почему мы ничего не пишем внутри этой функции, для меня это как указать функцию ЕСЛИ() без аргументов внутри.
В данном конкретном случае можно указывать функцию, принимающую единственный аргумент, без указания аргументов.
Три эквивалентных записи
Код
List.Transform(list, Number.From)
List.Transform(list, each Number.From(_))
List.Transform(list, (x)=>Number.From(x))

Причины, почему так можно делать, довольно любопытные, но выходят за рамки ответа :)
F1 творит чудеса
 
о как :) одновременно писали :)
Не в плане сокращения кода, но в плане эстетизьму:
Код
each 
    {
        _, 
        (x) as nullable duration=> 
            if x = null 
            then null 
            else
                let 
                    t = List.Transform(Text.Split(x, ":"),Number.From) 
                in 
                    #duration(0, 0, t{0}, t{1})
    }
F1 творит чудеса
 
Максим Зеленский, две простыни прям почти слово в слово!  :D
Только про нумерацию в списках, я забыл ответить.
Изменено: PooHkrd - 02.04.2020 12:51:55
Вот горшок пустой, он предмет простой...
 
Благодарю вас за развёрнутый ответ, мне понадобится время, чтобы это переварить. Что можете порекомендовать для изучения M?

Цитата
PooHkrd написал:
Добавьте в функцию проверку на пустые значения типа такого:
В том то и дело, что я не понимаю как это сделать. Сейчас подумаю.
Благодарю!  
Изменено: ivanka - 02.04.2020 14:22:16
 
ivanka, см. код в #16 и #18, там добавлена проверка
F1 творит чудеса
 
Максим Зеленский, PooHkrd, благодарю за помощь!

Для будущих поколений, решение:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([#"№"] <> null and [#"№"] <> "№")),
    #"Замененное значение" = Table.ReplaceValue(#"Строки с примененным фильтром","",null,Replacer.ReplaceValue,{"Время снимка  (план)", "Время снимка  (факт)"}),
    #"Добавленный префикс" = Table.TransformColumns(#"Замененное значение", List.Transform( {"Время снимка  (план)", "Время снимка  (факт)"}, each { _, (x)=> if x = null then null else let t = List.Transform(Text.Split(x, ":"),Number.From) in #duration(0, 0, t{0}, t{1}), type duration}))
in
    #"Добавленный префикс"

Прикладываю файл "Решение", решение находится в запросе "Решение 4 с проверкой на null"
Изменено: ivanka - 02.04.2020 14:51:06
Страницы: 1
Наверх