Forum Discussion

Helper III

5 years ago

Solved

Multiple queries on custom column ?

Hi Experts,

Sorry if the subject is misleading

I have a custom column with the following Step1:

= Table.TransformColumnTypes(#"Added Custom",{{"Postcode", type text}})

The out is basically postal address, but there are some "," which I want to replace with " " I am guessing I have to use the following function

Text.Replace(text as nullable text, old as text, new as text) as nullable text

Is this way ?

 Text.Replace(#"Added Custom", ",", " ")

This is what power BI shows, when I did that separately Step2:

= Table.ReplaceValue(#"Changed Type",",","",Replacer.ReplaceText,{"Full Address"})

Please can you help me to combine step1 and step2 with one step ? Is that possible?

Thank you

Why the need to do it as 1 step? Just curious.

Try this

= Table.ReplaceValue(Table.TransformColumnTypes(#"Added Custom",{{"Postcode", type text}}),",","",Replacer.ReplaceText,{"Full Address"})

regards

Phil

PhilipTreacy
Super User
5 years ago
Hi acerNZ
Why the need to do it as 1 step? Just curious.
Try this

= Table.ReplaceValue(Table.TransformColumnTypes(#"Added Custom",{{"Postcode", type text}}),",","",Replacer.ReplaceText,{"Full Address"})

regards
Phil
- acerNZ
  Helper III
  5 years ago
  Thank you PhilipTreacy It is just to concatenate to form full address as the columns were split up ( house, street, region, state, zip code, etc). I was about to to extend the logic of merging queries to a step prior with concatenation as well. Best Regards
PhilipTreacy
Super User
5 years ago
Hi acerNZ
So did my code work for you?
regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
acerNZ
Helper III
5 years ago
Hi PhilipTreacy
Thanks a lot. Yes it did work and apologies, did not notice there was a solution next line, I realized this just now.