Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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
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
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=")
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"
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.
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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.