Как вы можете заставить Power Query / Excel отделить и связать два уникальных идентификатора из одного столбца?

#excel #vba #powerbi #powerquery #m

#excel #vba #пауэрби #powerquery #m #powerbi

Вопрос:

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

Я хочу использовать Power Query для разделения Person_ID в один столбец и Job_ID в другой столбец. Люди связаны с работой, которая кажется им наиболее близкой над ними. Идентификаторы заданий представляют собой текстовую строку из 6 символов, идентификаторы сотрудников — из 9 символов. Один и тот же Job_ID может применяться к нескольким пользователям, но Person_ID уникален (только одно задание на человека, несколько человек для некоторых заданий).

Пример структуры данных:

введите описание изображения здесь

Надеюсь, у кого-то что-то есть!

1. Я слишком новичок, чтобы добавлять изображение, может быть, это поможет… photos.app.goo.gl/MLcjAsJLhwT8XCaaA

2. Что вы исследовали / пробовали до сих пор, и почему это не работает?

3. Я искал способы использования Text.Length и условных обозначений, чтобы определить, с какого числа какого типа начинать, но возникли проблемы с синтаксисом. Затем мне нужно что-то вроде смещения, чтобы проверить значения в ячейках над каждой ссылочной ячейкой (идентификатор пользователя), чтобы найти ближайший идентификатор задания. Эти значения должны были бы копировать или сводить в новый столбец. Последним шагом будет фильтрация ссылочного столбца, чтобы это был просто идентификатор пользователя, а новый столбец — просто идентификатор задания. Бьюсь об заклад, есть более простой способ. Вероятно, я мог бы создать для этого макрос VBA, но я бы предпочел использовать power query / M, поскольку именно он получает данные.

Ответ №1:

Шаг за шагом

Выделите входные данные

Данные…Из таблицы / Ассортимента… не проверяйте [], что в моей таблице есть заголовки

Добавить столбец…Пользовательский столбец… используя пользовательское имя столбца с формулой

 Text.Length([Column1])

Добавить столбец…Пользовательский столбец… используя имя столбца Custom.1, с формулой

 if [Custom]=6 then [Column1] else null

Нажмите на столбец Custom.1, щелкните правой кнопкой мыши и выполните заполнение … вниз…

Используйте стрелку рядом с пользовательским столбцом и снимите флажок [] 6, оставив только [x] 11

Щелкните пользовательский столбец, щелкните правой кнопкой мыши и выберите удалить столбцы

файл … закройте и загрузите

Созданный код:

 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Length([Column1])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]=6 then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom.1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Custom] =11)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in #"Removed Columns"