Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
tackytechtom
Super User
Super User

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 in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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

 

Anonymous
Not applicable

try use the function

Text.PadEnd(text as nullable text, count as number, optional character as nullable text) as nullable text
tackytechtom
Super User
Super User

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 in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

I like @tackytechtom 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors