Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

create a custom query to remove phrases

Hi all, 

 

Thank you for reading my post. I'm still learning lots with Power BI so sorry if this is a simple question to answer. 

 

I'm trying to create a custom query to remove various phrases from my columns, Ideally I would like to embed the following into a custom function: 

#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns","?","",Replacer.ReplaceText,)
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," "," ",Replacer.ReplaceText),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","-","",Replacer.ReplaceText),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","&",",",Replacer.ReplaceText),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value3",{{Text.Trim, type text}})
 
Any help on this matter would be greatly appreciated. 
 
Kind regards, 
Karlos O'Neill. 
Need Help
Tutorial Requests
  • ziying35's avatar
    ziying35
    6 years ago
    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WSjZUslJKTExJt1dTNjQzsFZLzC2wLk4sTtctTgeSCgpKOkrJRqhqUKRrYwE=",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (txt)=>let replaced_txt= List.Accumulate({{"?",""}, {" "," "}, {"&",","}, {"-",""}},txt,(s,c)=>Text.Replace(s,c{0},c{1})) in Text.Trim(replaced_txt),
    result = Table.TransformColumns(Source,{},fx)
in
    result

    Do you want to replace all the column data in the table in batches, and finally perform trim operation?

    Did I answer your question? Mark my post as a solution!

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    https://www.poweredsolutions.co/2019/02/19/parameters-and-functions-in-power-bi-power-query-custom-functions/

     

    I believe you want:

    (ReplacementText as text)  as text ->
    let
        #"Replaced Value" = Table.ReplaceValue(ReplacementText,"?","",Replacer.ReplaceText,)
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," "," ",Replacer.ReplaceText),
        #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","-","",Replacer.ReplaceText),
        #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","&",",",Replacer.ReplaceText),
        #"Trimmed Text" = Table.TransformColumns(#"Replaced Value3",{{Text.Trim, type text}})
    in
        #"Trimmed Text"
  • ziying35's avatar
    ziying35
    Impactful Individual
    6 years ago
    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WSjZUslJKTExJt1dTNjQzsFZLzC2wLk4sTtctTgeSCgpKOkrJRqhqUKRrYwE=",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (txt)=>let replaced_txt= List.Accumulate({{"?",""}, {" "," "}, {"&",","}, {"-",""}},txt,(s,c)=>Text.Replace(s,c{0},c{1})) in Text.Trim(replaced_txt),
    result = Table.TransformColumns(Source,{},fx)
in
    result

    Do you want to replace all the column data in the table in batches, and finally perform trim operation?

    Did I answer your question? Mark my post as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yeah, that is exactly what I'm trying to do. 

      • ziying35's avatar
        ziying35
        Impactful Individual
        6 years ago

        Anonymous 

        If any of the above code solves your problem, can you mark it as a solution?