Transforming Column

[Anonymous]

How can I transform the above column for each company so that the 'Payroll Subservices' are on one line for each company separated by a semi colon? so for instance the first record should read:

Co           Payroll Subservices                                Start Date      End Date

3385       Payroll-Full;Checks-Full;NewHire-Mini    1/4/2002      12/31/2100

[OwenAuger · Accepted solution]

@Anonymous

The way I would do it is by grouping your table by the appropriate columns (all except "Payroll Subservices"?) and aggregating Payroll Subservices using Text.Combine.

Here is a dummy query illustrating how it can be done:

let
    // Create dummy table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMja2MFXSUQpIrCzKz8nRdSvNyQFyTfQN9Y0MDIyATGNDfUMjfSNDAwOlWB24eueM1OTsYphyQ4hyQ5zK/VLLPTKLUnV9M/My8RpvABFFcw5u86HqiXUOULkFpvHGUA3mODXA3I9qgRma+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Co = _t, #"Payroll Subservices" = _t, #"Service Start Date" = _t, #"Service End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Co", type text}, {"Payroll Subservices", type text}, {"Service Start Date", type date}, {"Service End Date", type date}}),

    // Group by all columns except Payroll Subservices
    #"Grouped Rows" =
      Table.Group(
        #"Changed Type",
        List.RemoveItems(Table.ColumnNames(#"Changed Type"), {"Payroll Subservices"}),
        {{"Payroll Subservices", each Text.Combine([Payroll Subservices],";"), type text}}
    )
in
    #"Grouped Rows"

The important step is "Grouped Rows", and you would want to add a similar step to your existing query.

The code in red specifies the grouping columns, which I assumed to be all except "Payroll Subservices"

The code in green specifies how "Payroll Subservices" is to be aggregated, with Text.Combine using a semicolon delimiter.

You may want to re-order the columns as well, as the combined "Payroll Subservices" column ends up last.

Regards,

Owen