How to use PQ to expand items in one specific column into separate columns

[ubergreen]

I have values in column B that I want to expand out into their own individual columns but all be on the same row. The only solutions that I have found so far ends up affecting all of the other rows/columns during the process, so I haven't been able to find a solution that can allow me to apply this action to only one specific column in my worksheet.

First image is how the original data looks.

Second image is how I want it to look afterwards.

 

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    func = (tbl) => 
        [cols = Table.ToColumns(tbl),
        cols_updated = 
            {cols{0}} & 
            List.Transform(List.RemoveNulls(cols{1}), (x) => {null, x}) & 
            List.Skip(cols, 2),
        res = Table.FromColumns(cols_updated)][res],
    group = Table.Group(
        Source, 
        "Column1",
        {"x", func}, 
        GroupKind.Local, 
        (s, c) => Number.From(c <> null)
    ),
    z = Table.Combine(group[x])
in
    z

 

[ubergreen]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    func = (tbl) =>
        [cols = Table.ToColumns(tbl),
        cols_updated =
            {cols{0}} &
            List.Transform(List.RemoveNulls(cols{1}), (x) => {null, x}) &
            List.Skip(cols, 2),
        res = Table.FromColumns(cols_updated)][res],
    group = Table.Group(
        Source,
        "Column1",
        {"x", func},
        GroupKind.Local,
        (s, c) => Number.From(c <> null)
    ),
    z = Table.Combine(group[x])
in
    z

Thanks for the reply. I am still fairly new to PQ. Do you think you could break down what is happening with each line so that I can understand it better and be able to apply it?

Simply copy/pasting it into my workbook isn't working, but it is probably because I am missing some sort of basic concept to implement it correctly, such as table or workbook naming variances.

 

[AlienSx]

@ubergreen , in short, we group data by non-null values in column A and replace column B with several columns of {null, column_B_value} combinations.
In Excel select all data, press Ctrl-T to create table (uncheck "my data has headers" option), make sure that table name is Table1 and load this table into PQ. In Advanced Editor replace all code that loads your table with mine.