Replace character in list of lists, between first comma and specific string

[Digger]

I have bad csv files, and need first replace commas in peoples names

so i do

= Table.AddColumn(#"Added Custom1", "Custom", each Lines.FromBinary([Content]) )

And geting Column of list values

Now in each List i need to replace or delete comma between each line first comma and ",SB"

To remove commas from customers names to get good csv

So how to do that in PBI?

Closest expected result is like

List.ReplaceMatchingItems(

{Source List}, { {OldValue1, NewValue1}, {OldValue2, New Value2} }

)

But it need manual intervention each time if new weird name appears again.

[Syndicate_Admin · Accepted solution]

Solved by

Table.PromoteHeaders(
	Table.FromList(
		List.Transform(
			Lines.FromBinary([Content])
				,each 
					try
						Text.Combine({
							Text.BeforeDelimiter(_,","), ",", 							// text till first comma
							Text.Replace(Text.BetweenDelimiters(_,",",",SB"),",","."),	// replacing comma to dot only in text between first comma and string ",SB"
							Text.Middle(_,Text.PositionOf(_,",SB"),Text.Length(_))		// Text from ,SB till end
                        })
					otherwise _															// ignore headers error
       )
	, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error					// split csv by columns
	)
, [PromoteAllScalars=true]																// promote headers
)

[Shaurya]

Hi @Digger,

I suggest that you replace the commas with a space and then TRIM the text to remove any leading or trailing spaces.

#"Replaced Value" = Table.ReplaceValue(#"Changed Type",","," ",Replacer.ReplaceText,{"Name"})

Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog: Forecast Period - Previous Forecasts

[Digger]

@Shaurya  it will not works, as first i have lists not table, second i need replace commas between strings ,not leading or trailing spaces

[v-yiruan-msft]

Hi @Digger ,

Please review the following links and check if they can help you solve the problem. Or could you please share that csv file with us and your expected result with some special examples? Later we can give you a suitable solution base on your provided info. Thank you.

Power Query - Replace multiple substrings in one list column

//Do the actual replacements using the TransformColumns method
    #"Replace Multiple" = Table.TransformColumns(#"Previous Step", 
    {"Column1", (s)=> 
        Text.Combine(
            List.ReplaceMatchingItems(
                List.Transform(Text.Split(s,","), Text.Trim),
                #"Replacement List"),
            ",")
    })

Multiple replacements or translations in Power BI and Power Query

Best Regards