Concatenate Values of the same Column

[alex9999]

Hi, 

I have been trying to solve this issue for a couple of hours and thought my best bet would be to contact the community for some help. I am using a dataset which has 2 important values that should essentially be in separate columns. I have created a dataset below to give an idea of what I am trying to achieve:

Resource	Skill/Type
<none>	E10641067
Joe Bloggs	GRADE 5_LCL
Joe Jones	GRADE 5_TRA
<none>	E20612947
Sam Blue	GRADE 5_LCL
Sam Blue	GRADE 5_TRA

I basically need to concatenate the "E" numbers to the beginning of the GRADE so that I can filter them out in a report.

It sounds logically impossible in my head - however I am quite unfamiliar with power query so who knows.

Any help would be appreciated.

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBOzsvPSwWzUpV0lFwNDcxMgNhcKVYnWskrP1XBKSc/Pb0YKOUe5OjiqmAa7+PsA5f0AupFlgsJcgTLYTPZyMDM0MjSBGJycGIu0OTSVCzmYpECGxsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, #"Skill/Type" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Start([#"Skill/Type"],1)="E" and [Resource]="<none>" then Text.Middle([#"Skill/Type"],1) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Final Skill/Type", each if Text.Start([#"Skill/Type"],1)="E" and [Resource]="<none>" then [#"Skill/Type"] else [Custom]&[#"Skill/Type"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Skill/Type", "Custom"})
in
    #"Removed Columns"

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBOzsvPSwWzUpV0lFwNDcxMgNhcKVYnWskrP1XBKSc/Pb0YKOUe5OjiqmAa7+PsA5f0AupFlgsJcgTLYTPZyMDM0MjSBGJycGIu0OTSVCzmYpECGxsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, #"Skill/Type" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Start([#"Skill/Type"],1)="E" and [Resource]="<none>" then Text.Middle([#"Skill/Type"],1) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Final Skill/Type", each if Text.Start([#"Skill/Type"],1)="E" and [Resource]="<none>" then [#"Skill/Type"] else [Custom]&[#"Skill/Type"]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Skill/Type", "Custom"})
in
    #"Removed Columns"

[alex9999]

Hi @Vijay_A_Verma ,

I tried your solution. It is similar to the expected outcome. However, might I add that the "E" numbers (including the "E") will need to be in a separate column before the "GRADE".

Kind Regards

[Vijay_A_Verma]

Can you please post the expected output table / picture?

[alex9999]

Hi @Vijay_A_Verma ,

Below is an expected outcome from the query.

Cheers 

Resource	Skill	Type
<none>
Joe Bloggs	E10641067	GRADE 5_LCL
Joe Jones	E10641067	GRADE 5_TRA
<none>
Sam Blue	E20612947	GRADE 5_LCL
Sam Blue	E20612947	GRADE 5_TRA

[Vijay_A_Verma · Accepted solution]

Use below query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBOzsvPSwWzUpV0lFwNDcxMgNhcKVYnWskrP1XBKSc/Pb0YKOUe5OjiqmAa7+PsA5f0AupFlgsJcgTLYTPZyMDM0MjSBGJycGIu0OTSVCzmYpECGxsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, #"Skill/Type" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "SkillTemp", each if not (Text.Start([#"Skill/Type"],5)="GRADE") then [#"Skill/Type"] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Type", each if Text.Start([#"Skill/Type"],5)="GRADE" then [#"Skill/Type"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"SkillTemp"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Down", "Skill", each if [Resource]="<none>" then null else [SkillTemp]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Skill/Type", "SkillTemp"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Resource", "Skill", "Type"})
in
    #"Reordered Columns"