Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query. Создать настраиваемый столбец, который подсчитает количество заполненных столбцов в диапазоне
 
#1
21.10.2019 21:28:18
Мне необходимо получить в powerbi количество незаполненных ячеек в ряду. Используя PQ, можно ли это сделать?
То есть получить столбец, где будет видно кол-во пустых строк или заполненных. Пример диапазона во вложении.
В результате в PQ получить бы
столбец F всего ячеек. G заполненных. F пустых
Изменено: extrafant - 22.10.2019 09:42:48
 
 
 
#2
22.10.2019 10:32:23
Примерно так:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    AddedCustom = Table.AddColumn(Source, "Всего ячеек", each List.Count(Record.ToList(_))),
    AddedCustom1 = Table.AddColumn(AddedCustom, "Незаполненных ячеек", each [Всего ячеек] + 1 - List.NonNullCount(Record.ToList(_)))
in
    AddedCustom1
Вот горшок пустой, он предмет простой...
 
 
 
#3
23.10.2019 18:06:49
Спасибо! Просто с PQ все получилось, но когда я открыл этот запрос в power bi (отправил его на изменение, открылось окно PQ), то Ваша формула выдала ошибку. Я так догадываюсь это произошло от того, что не было "Таблица1" ? Ведь там я в экселе сам назвал мой диапазон Таблица1, превратив его в умную таблицу
Дополню!
Когда я "разворачиваю" столбцы, то у меня, получается, моя таблица дублируется. С этим можно что-то сделать? Или просто в запросе удалить лишние столбцы и не обращать внимания?
Изменено: extrafant - 23.10.2019 18:17:28
 
 
 
#4
24.10.2019 09:15:04
Цитата
extrafant написал:
Я так догадываюсь это произошло от того, что не было "Таблица1"
Правильно догадываетесь. В шаге Источник, вам нужно изменить способ получения таблицы. Я же не знаю откуда вы её в PBI тянете.
Цитата
extrafant написал:
Когда я "разворачиваю" столбцы, то у меня, получается, моя таблица дублируется.
В вашей задаче нигде про разворачивание столбцов нет, как и в моем решении. Если это отдельный вопрос, то по правилам форума необходимо создать отдельную тему с названием отображающим суть проблемы.
Вот горшок пустой, он предмет простой...
 
 
 
#5
24.10.2019 09:29:00
Пусть таблица "Диапазон.xlsx" лежит в любой папке. Условно рабочем столе.
Когда я использую предложенный Вами код, то в результате получается следующее (картинка во вложении). Однако, я ожидал, что просто добавится нужный подсчет
 
 
 
#6
24.10.2019 09:37:03
Как-то так:
Код
let
    Source = Excel.Workbook(File.Contents("E:\Диапазон.xlsx"), null, true),
    Navigation = Source{[Item="Отчет Заполнение личной карточк",Kind="Sheet"]}[Data],
    RemovedTopRows = Table.Skip(Navigation,6),
    RemovedBottomRows = Table.RemoveLastN(RemovedTopRows,3),
    PromotedHeaders = Table.PromoteHeaders(RemovedBottomRows, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"№ п/п", Int64.Type}, {"Фамилия", type text}, {"Имя", type text}, {"Отчество", type text}, {"Пол", type text}}),
    AddedCustom = Table.AddColumn(ChangedType, "Всего ячеек", each List.Count(Record.ToList(_))),
    AddedCustom1 = Table.AddColumn(AddedCustom, "Незаполненных ячеек", each [Всего ячеек] + 1 - List.NonNullCount(Record.ToList(_)))
in
    AddedCustom1
Вот горшок пустой, он предмет простой...
 
 
 
#7
27.10.2019 11:15:36
Так, почему-то ошибку выдает:

Expression.Error: Столбец "№ п/п" таблицы не найден.
Сведения:
   № п/п

При этом есть столбец А, где номер есть
 
 
 
#8
27.10.2019 11:40:25
extrafant, попробуйте, на худой конец, удалить эту часть в запросе
Код
{"№ п/п", Int64.Type},
 
 
 
#9
27.10.2019 13:27:24
Я так думаю, может я путь не тот указываю. Пробовал номер удалить. На фамилию стало ругаться. Вот такой запрос:
Код
let
    Source = Excel.Workbook(File.Contents("E:\extra\Загрузки\Отчет.xlsx"), null, true),
    Navigation = Source{[Item="Отчет Заполнение личной карточк",Kind="Sheet"]}[Data],
    RemovedTopRows = Table.Skip(Navigation,6),
    RemovedBottomRows = Table.RemoveLastN(RemovedTopRows,3),
    PromotedHeaders = Table.PromoteHeaders(RemovedBottomRows, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"№ п/п", Int64.Type}, {"Фамилия", type text}, {"Имя", type text}, {"Отчество", type text}, {"Пол", type text}}),
    AddedCustom = Table.AddColumn(ChangedType, "Всего ячеек", each List.Count(Record.ToList(_))),
    AddedCustom1 = Table.AddColumn(AddedCustom, "Незаполненных ячеек", each [Всего ячеек] + 1 - List.NonNullCount(Record.ToList(_)))
in
    AddedCustom1
При этом книга отчет лежит E:\extra\Загрузки
В книге отчет есть лист Отчет Заполнение личной карточк
Не понимаю, почему ошибка. А у остальных работает этот запрос?
 
 
 
#10
27.10.2019 16:17:09
Работает с выложенным вами примером
Возможно у вас в файлах разное количество строк сверху до таблицы? Тогда замена шага RemovedTopRows поможет
Код
RemovedTopRows = Table.Skip(Navigation,each [Column1]<>"№ п/п"),
 
 
 
#11
28.10.2019 18:33:32
Код
let
    Source = Excel.Workbook(File.Contents("E:\extra\Загрузки\Отчет.xlsx"), null, true),
    Navigation = Source{[Item="Отчет Заполнение личной карточк",Kind="Sheet"]}[Data],
    RemovedTopRows = Table.Skip(Navigation,each [Column1]<>"№ п/п"),
    RemovedBottomRows = Table.RemoveLastN(RemovedTopRows,3),
    PromotedHeaders = Table.PromoteHeaders(RemovedBottomRows, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"№ п/п", Int64.Type}, {"Фамилия", type text}, {"Имя", type text}, {"Отчество", type text}, {"Пол", type text}}),
    AddedCustom = Table.AddColumn(ChangedType, "Всего ячеек", each List.Count(Record.ToList(_))),
    AddedCustom1 = Table.AddColumn(AddedCustom, "Незаполненных ячеек", each [Всего ячеек] + 1 - List.NonNullCount(Record.ToList(_)))
in
    AddedCustom1
Вот так заменил. Все-равно ошибка.
Добавил исходный файл!

Я так понимаю, что в код нужно прописать все поля? Но почему тогда ошибку выдает на столбец 1, а не на столбец, который не внесен в код?
 
 
 
#12
28.10.2019 18:59:40
extrafant, может причина в том что таблицы в файлах Диапазон.xlsx и Отчет2.xlsx разные.
попробуйте,опять же на худой конец, удалить в запросе шаг ChangedType
 
 
 
#13
28.10.2019 19:12:23
Код
let
    Source = Excel.Workbook(Web.Contents("https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=364632&action=download"), null, true),
    Navigation = Source{[Item="Отчет Заполнение личной карточк",Kind="Sheet"]}[Data],
    RemovedTopRows = Table.Skip(Navigation,each [Column1]<>"№ п/п"),
    RemovedBottomRows = Table.RemoveLastN(RemovedTopRows,each (try Number.From([Column1]) otherwise null)=null),
    PromotedHeaders = Table.PromoteHeaders(RemovedBottomRows, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"№ п/п", Int64.Type}}&List.Zip({List.Skip(Table.ColumnNames(PromotedHeaders)),List.Repeat({type text},Table.ColumnCount(PromotedHeaders)-1)})),
    AddedCustom = Table.AddColumn(ChangedType, "Всего ячеек", each List.Count(Record.ToList(_))),
    AddedCustom1 = Table.AddColumn(AddedCustom, "Незаполненных ячеек", each [Всего ячеек] + 1 - List.NonNullCount(Record.ToList(_)))
in
    AddedCustom1
 
 
 
#14
29.10.2019 19:48:11
Запрос сработал!
Единственное, я завтра попробую вместо url адреса запрос сделаю из папки, поменяв source Но, надеюсь, что проблем не возникнет! Как сделаю, отпишусь! Но я почти уверен, что должно же оно сработать!

Я начинаю понимать в чем моя ошибка. Она здесь
Код
Source = Excel.Workbook(Web.Contents("https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=364632&am...), null, true),
Итак мои шаги
1. я сохраняю в папку загрузки книгу отчет (получаю исходник)
2. создаю новую книгу. В будущем я в нее будут получать данные
3. Нажимаю получить данные из excel
4. Добавляю настраиваемый столбец и вставляю код, который мне подсказали использовать.
Вопрос, на что поменять source при том, как я получаю свой файл. Сейчас же запрос, понятное дело из интернета. А когда я отправлял в папку загрузки (сейчас я это понял) я получал две таблицы
Изменено: extrafant - 29.10.2019 20:09:04
 
 
 
#15
30.10.2019 02:53:10
Цитата
extrafant написал:
начинаю понимать в чем моя ошибка. Она здесь
Не правильно понимаете. Ошибка в том, что запрос был написан для файлов одной структурой, а вы ему суете совершенно другую.
1. В файле диапазон над таблицей было 3 ненужные строки, которые удалялись на шаге RemovedTopRows , в файле отчет их нету. Эту проблему решил заменой количества удаляемых строк функцией, которая удаляет строки, пока в первом столбце не обнаружится заголовок "№ п/п". Если этот столбец будет переименован, запрос перестанет работать.
2. В файлах Диапазон и Отчет разные названия столбцов, из-за этого сыпались ошибки на шаге ChangedType. Эту проблему решил генерацией динамического списка на основе названий столбцов.
Цитата
extrafant написал:
на что поменять source при том
да на что душе угодно, хоть на
Код
File.Contents("E:\extra\Загрузки\Отчет.xlsx")

Я использовал url, дабы не качать, ибо оно мне не надо
 
 
 
Страницы: 1
Наверх