Search code examples
excelvbapowerbipowerquerym

How can you get Power Query/Excel to separate and associate two unique IDs from the same column?

I have a dataset that exports with a single column including personnel IDs and job IDs.

I want to use Power Query separate Person_ID into one column and Job_ID into another column. People are associated with the job that appears closest above them. Job IDs are a 6-character text string, Person IDs are 9 character. The same Job_ID can apply to multiple people, but Person_ID is unique (only one job per person, multiple people for some jobs).

Example data structure:

enter image description here

Hope someone's got something!

Solution

  • Step by step

    Highlight input data

    Data...From Table/Range... do not check [] my table has headers

    Add Column...Custom Column... using column name Custom, with formula 

    Text.Length([Column1])

    Add Column...Custom Column... using column name Custom.1, with formula 

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

    Click on Custom.1 column, right click and do fill...down...

    Use arrow next to Custom column and uncheck [] 6 leaving just [x]11

    Click column Custom, right click and choose remove columns

    file...close and load

    Code produced:

    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"