Power Query - Delete First Line Feed (#lf)

[Caleeco]

Hello

I’ve done some googling but come up blank! I have a Power Query where I replace all instances of ‘*’ with a #(lf).

However, all my rows now have a leading line feed. Is it possible to delete just the first instance of the line feed and leave the others in place?

Cheers
Caleeco

 

[sandy666]

if I understand well, try Table.Skip(Source,1)
or show example of you table

 

[Caleeco]

Hi Sandy,
Thanks for the reply. Apologies if my question wasn’t clear. Clarification:

• I don’t want to skip an entire record
• Each record has a column with a text field
• In each text entry (on each record). There is a leading #(LF) line feed in the text
• I want to delete said line feed but leave all others in the same record intact

 

[sandy666]

so you've something like:
#(lf) blabla bla blabla
?

 

[sandy666]

you can try
Table.ReplaceValue
eg. Table.ReplaceValue([I]previous_step[/I]," #(lf) blabla bla blabla","blabla bla blabla",Replacer.ReplaceText,{"[I]column_where_is_value_to_replace[/I]"})
if not, post representative example of source data and expected result from this source

 

[Caleeco]

Hi Sandy,

Thanks for sticking with me. Example below

Step	Step	Resultant Text
1	Import Data	* Product ID * Product Name * Signature
2	Replace all instances of '* ' with '#(lf)'	#(lf)Product ID #(lf)Product Name #(lf)Signature
3	Current Output in one cell in Excel	(blank line) Product ID Product Name Signature

So you see when I export to excel, and wrap text the first Line Feed creates a blank line of text in the cell. So I would like to add a step after Step 2, to remove only the first line feed in the string.

Is this possible?

Kind Regards
Caleeco

 

[sandy666]

like this?

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    TrimStart = Table.AddColumn(Source, "Custom", each Text.TrimStart([Import],"*")),
    Trim = Table.TransformColumns(TrimStart,{{"Custom", Text.Trim, type text}}),
    Replace = Table.ReplaceValue(Trim,"*","#(lf)",Replacer.ReplaceText,{"Custom"}),
    TSC = Table.SelectColumns(Replace,{"Custom"})
in

TSC

 

[Caleeco]

Yes! That's what I was after

Thank you for the M code, I will test this when I'm at work tomorrow and let you know how i get on!

Cheers
Caleeco

 

[sandy666]

You are welcome
I think first what you should do is replace space*space to* because of the spaces in front of second and third line
so it will like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Rep = Table.ReplaceValue(Source," * ","*",Replacer.ReplaceText,{"Import"}),
    TrimStart = Table.AddColumn(Rep, "Custom", each Text.TrimStart([Import],"*")),
    Trim = Table.TransformColumns(TrimStart,{{"Custom", Text.Trim, type text}}),
    Replace = Table.ReplaceValue(Trim,"*","#(lf)",Replacer.ReplaceText,{"Custom"}),
    TSC = Table.SelectColumns(Replace,{"Custom"})
in
    TSC

 

[Caleeco]

Hi Sandy,

Just tested this at work and it's perfect! Thank your help, you saved me a lot of time & headache! haha

Cheers
Caleeco