Forum Discussion

alex9999's avatar
alex9999
Helper I
4 years ago
Solved

Concatenate Values of the same Column

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:

 

ResourceSkill/Type
<none>E10641067
Joe BloggsGRADE 5_LCL
Joe JonesGRADE 5_TRA
<none>E20612947
Sam BlueGRADE 5_LCL
Sam BlueGRADE 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.

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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's avatar
    Vijay_A_Verma
    4 years ago

    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"

5 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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's avatar
      alex9999
      Helper I
      4 years ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        Can you please post the expected output table / picture?