Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Преобразование таблицы в Power BI
 
#1
04.02.2021 21:33:05
Имеется Исходная таблица, см. в конце сообщения. Во второй строке указал автоматически определяемый при загрузке тип по BI. Число N (количество этапов) меняется от отчета к отчету в диапазоне от 40 до 100. По непонятной причине, BI при загрузке таблицы из Excel у половины колонок Длит ставит type any вместо type number.

Подскажите, как можно решить две задачи:

1. Всем колонкам Длит присвоить type number автоматически

2. Автоматически собрать из исходной таблицы новую, с колонками:
Шаг| Исполнитель| Длит| ШЗ
т.е. все блоки по 4 столбца Исходной таблицы нужно сложить чем-то навроде Table.Combine.

Сильно тапками не кидайте, я в Power Query новичок :(

Исходная таблица:
Шаг1| Исполнитель1| Длит1| ШЗ_1| Шаг2| Исполнитель2| Длит2|ШЗ_2| ... ... ... |ШагN| ИсполнительN| ДлитN| ШЗ_N|
ABCABC1.2ABCABCABC1.2ABCABCABCanyABC
Изменено: Михаил Семенов - 04.02.2021 21:35:56
 
 
 
#2
04.02.2021 22:10:10
Доброе время суток.
Первая ступень
Цитата
2.3. Приложите файл(ы) с примером (общим весом не более 300Кб) в реальной структуре и форматах данных того, что есть сейчас и того, что хотелось бы на выходе.
 
 
 
#3
05.02.2021 09:28:39
Файлы Excel и BI прилагаю. Первый является источником для второго.

Первую задачу не получается довести до конца. Получить список названий колонок для изменения типа не проблема:
List.Select(Table.ColumnNames(Исходник),  each Text.Contains(_,"Длит")= true)
Далее, используя List.Split, можно получить список списков.
Насколько понимаю, в этот список списков, в каждый вложенный список, нужно добавить type number и заставить Table.TransformColumnTypes понять этот список списков. На этом я застопорился.

Ко второй задаче преобразования таблицы не понимаю как подступиться. В Excel сделал бы через циклы, но мне нужно в Power BI.
 
 
 
#4
05.02.2021 10:29:06
Вариант
Код
let
    Source = Excel.Workbook(File.Contents("C:\Temp\path\TMP.xlsx"), null, true){[Item="Исходник",Kind="Sheet"]}[Data],
    setHeader = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    selectColumns = List.Split(Table.ColumnNames(setHeader), 4),
    renameColumns = List.Transform(selectColumns, each List.Zip({_, {"Роль", "Состояние", "Длит", "Шз"}})),
    zip = List.Zip({selectColumns, renameColumns}),
    toSubTables = List.Transform(zip, each Table.RenameColumns(Table.SelectColumns(setHeader, _{0}), _{1})),
    toTable = Table.Combine(toSubTables),
    setTypes = Table.TransformColumnTypes(toTable, {{"Роль", type text}, {"Состояние", type text}, {"Шз", type text}, {"Длит", type number}}),
    notEmpty = Table.SelectRows(setTypes, each [Роль] <> "" and [Роль] <> null)
in
    notEmpty

Думаю, с добавлением столбца номера строки ID справитесь самостоятельно :)
 
 
 
#5
05.02.2021 16:33:29
Спасибо! С индексом разберусь :). Пока вникал, мозги вскипели и возникли вопросы:

1. Table.TransformColumnTypes не примет список списков, т.к. type text не должно быть текстовым значением, а в список его можно загнать только в виде текста? Или есть метод изменения типа столбца таблицы подстановкой списка в Table.TransformColumnTypes?

2. Ваши методы работы с таблицами и списками  в PQ где то описаны, хотя бы основы, или только своим умом и на примерах можно учиться? Если есть источники, подскажите пожалуйста где можно ознакомиться?
 
 
 
#6
05.02.2021 16:51:52
Цитата
Михаил Семенов написал:
1.
Странное утверждение, учитывая, что надо задавать тип данных, а не текстовое название. Всё же стоит посмотреть, что пишет Power Query при ручной простановке типов данных - разве там текстовое имя типа?
И, любую задачу следует начинать с определений, что есть что.
Код
let
    Source = Excel.Workbook(File.Contents("C:\Temp\TMP.xlsx"), true, false){[Item="Исходник",Kind="Sheet"]}[Data],
    columnNames = Table.ColumnNames(Source),
    // числовые только столбцы, начинающиеся с Длит
    numberColumnNames = List.Select(columnNames, each Text.StartsWith(_, "Длит")),
    // остальные - текстовые
    textColumnNames = List.RemoveItems(columnNames, numberColumnNames),
    // преобразуем список числовых столбцов в список: имя, числовой тип
    numberColumns = List.Transform(numberColumnNames, each {_, type number}),
    // преобразуем список текстовых стобцов в список: имя, текстовый тип
    textColumns = List.Transform(textColumnNames, each {_, type text}),
    // установим типы данных в таблице
    result = Table.TransformColumnTypes(Source, textColumns & numberColumns)
in
    result
Если есть источники, подскажите пожалуйста где можно ознакомиться?
Книги по Power Query, справочник функций, спецификация языка. :)  Ну, и алгоритмы и структуры данных.
 
 
 
Страницы: 1
Наверх