cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
STIBBS_807
Resolver I
Resolver I

Merge two field with one field at the end of the new field

I have two fields that I wish to merge into one.  However I would like the second field to appear at a end to the first field.  The issue is the first field is variable in length.  So here is my data.

ItemRecords
 1. Item221/259
 2. Item Brown218/259
 3. Item Red7/8
 4. Item No colour2/4
 5. Item White8/24

 

I would like it to appear like this.

STIBBS_807_1-1648157600964.png

 

 

How do I make the lengh of the item field a standard size (longer) so that the merge appends to the end of the Item field?   So the opposite of trimming the field I want to add spaces at the end of the Item Field.

 

1 ACCEPTED SOLUTION
tomfox
Community Champion
Community Champion

Hi @STIBBS_807 ,

 

Here is my solution, which only displays properly in i.e. notepad:

tomfox_0-1648161259611.png

 

And this is how it looks like in Power Query:

tomfox_2-1648161352708.png

 

Here the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUjDUU/AsSc1V0lEyMjLUNzK1VIrVAQobQYQVnIryy/NAkoYWCEljqGRQagpQylzfAiJsAhX2y1dIzs/JLy0C6dM3gUiaQiXDMzJLUoESQOOAMrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Records = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "ItemColumnLength", each Text.Length([Item])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"ItemColumnLength", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Item] & "  " & Text.Repeat(" ", (List.Max(#"Changed Type"[ItemColumnLength] ) -[ItemColumnLength]  )) & [Records]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ItemColumnLength"})
in
    #"Removed Columns"

 

Does this solve your issue? 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

View solution in original post

5 REPLIES 5
STIBBS_807
Resolver I
Resolver I

I replaced the spaces with a period and a space to make it look like a title page.

wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1648194116083.png

 

Rocco_sprmnt21
Community Champion
Community Champion

try use the function

Text.PadEnd(text as nullable text, count as number, optional character as nullable text) as nullable text
tomfox
Community Champion
Community Champion

Hi @STIBBS_807 ,

 

Here is my solution, which only displays properly in i.e. notepad:

tomfox_0-1648161259611.png

 

And this is how it looks like in Power Query:

tomfox_2-1648161352708.png

 

Here the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUjDUU/AsSc1V0lEyMjLUNzK1VIrVAQobQYQVnIryy/NAkoYWCEljqGRQagpQylzfAiJsAhX2y1dIzs/JLy0C6dM3gUiaQiXDMzJLUoESQOOAMrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Records = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "ItemColumnLength", each Text.Length([Item])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"ItemColumnLength", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Item] & "  " & Text.Repeat(" ", (List.Max(#"Changed Type"[ItemColumnLength] ) -[ItemColumnLength]  )) & [Records]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ItemColumnLength"})
in
    #"Removed Columns"

 

Does this solve your issue? 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

I like @tomfox solution. Just be aware it works well with a mono-spaced font like Courier, but may not work in a font like Arial that has kerning.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors