Replace 1st and 2nd occurrence of a comma with different values

[fred65]

Hi All,
I am trying to work out how I can replace each occurance with different text values. I tried something like this but it gives an error.

Text.Replace(
    Text.BeforeDelimiter([Address], ",", {1, RelativePosition.FromEnd})
    ,"&state=")

Example:
'Sydney, NSW, Australia'
I want the comma's to be replaced as such:
'Sydney&state=NSW&country=Australia'

The error was Expression.Error: We cannot convert a value of type List to type Text.
Appreciate any assistance!
Fred

[slorin · Accepted solution]

Hi,

= Text.Combine(List.Transform(List.Zip({{"", "&state=", "&country="}, Text.Split(Your_Text,", ")}), Text.Combine))

"Sydney, NSW, Australia"

-->  "Sydney&state=NSW&country=Australia"

", NSW, Australia"

--> "&state=NSW&country=Australia"

Stéphane

[watkinnc]

But as long as there is at least one city value, the split will work, leaving nulls where there is no city in the City column.  You would just augment your formula from

each [City]&[State]&[Country]

to

each if [City] = null then [State]&[Country] else [City]&[State]&[Country]

Also, I actually did say split by ", ", because that's how it it written in your example: 'Sydney, NSW, Australia'.  HYou don't want to keep the space, I would assume.

This really does seem like the easiest way.

--Nate

[spinfuzer]

0 is the first position and 2 is the number of characters.  Since I saw ", " I assumed you wanted to replace the comma and the space.

Text.ReplaceRange([Address],Text.PositionOf([Address], ",", 0),2, "&state=")

[fred65]

Hi Spinfuzer.
I really like that and would like to work with that but I am getting the same convert List to text error.

Here is my code if you or anyone is able to assist further. I'm not the most experienced in Mashup!

let
    Source = Excel.Workbook(File.Contents("anexcelfile.xlsx"), null, true),
    #"Projects_Sheet" = Source{[Item="Projects",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"Projects_Sheet",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Blank Rows" = Table.SelectRows(#"Promoted Headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Production", Percentage.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Location", "Location - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Location - Copy", "Address"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","#(lf)","",Replacer.ReplaceText,{"Address"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," ","",Replacer.ReplaceText,{"Address"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each Text.Length([Address])-Text.Length(Text.Replace([Address],",",""))+1),
    #"Insert Text Range" = Text.ReplaceRange(#"Added Custom"[Address],Text.PositionOf(#"Added Custom"[Address], ",", 0),2, "&state=")
in
    #"Insert Text Range"

[spinfuzer]

You either need to use Text.ReplaceRange in Table.TransformColumns or Table.AddColumn.  You could also do a List iterator like List.Generate or List.Transform as well and then add the column with Table.FromColumns to your main table.

[fred65]

Hi Nate,

Thanks for that, I did think of that but not all rows have a city. Some rows just have the state and country.

Fred

[watkinnc]

If I were you, for future debugging, I would split that value by the delimiter ", " (comma space), and then use Add Prefix "&state" and "&country" from the menu on your state and country columns.  Then in whatever formula you use, you can just say 

each [City]&[State]&[Country]

--Nate