cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mrainey Member
Member

Transforming Column

1.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Transforming Column

@mrainey

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
Super User
Super User

Re: Transforming Column

@mrainey

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!