Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Combine replace value steps?

Me again,

 

I am combining steps to clean up my Query Editor. E.g. Renaming columns all in one step rather than all over the shop. 

I want to combine Table.ReplaceValue steps but it doesn't seem to want to. I assume you cannot do this for some reason, but if you know how then please let me know. 

 

I tried copying one of my replace values onto the end of another, separated by a comma with the parenthesis wrapping all of it. 

This didn't work. 

 

 

Need Help

8 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Anonymous It is possible, Please try this...

     

    Renaming Mutliple columns in single step:

     

      #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Product", "Product1"}, {"Tank", "Tank1"}, {"Inventor", "Inventor1"}}),

    Mutliple Replace values in single step:

     

        #"AllReplace" = [#"DIESEL" = "DIESEL1",#"REG" = "REG1"],
    #"Replaced Value" = Table.TransformColumns(#"Renamed Columns",{{"Product1",each Record.FieldOrDefault(AllReplace,_,_)}})
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi there Pattem,

       

      I can't figure this out. 

       

      In the step i've pasted below I am is replacing </font> with blank. (some crazy formatting was pulled into my BI report)

      My next replace would be for example <font> with blank

      After that I want to replace <font face> with blank

       

      In total, I have 20 replaced values. I want them all in one step. Below is the first step, if you can show me what to type instead of what is below, I can probably follow. I'm just getting used to Power BI and Query Editor still!

       

      = Table.ReplaceValue(#"Replaced Value","</font>","",Replacer.ReplaceText,{"Claim ID/Invoice_x00"})

      • PattemManohar's avatar
        PattemManohar
        Community Champion
        7 years ago

        Anonymous Please provide some sample data so that I can provide you the exact steps as per that. Meanwhile, I'll make it clear about the steps I've provided 

         

            #"AllReplace" = [#"DIESEL" = "DIESEL1",#"REG" = "REG1"],
    #"Replaced Value" = Table.TransformColumns(#"Renamed Columns",{{"Product1",each Record.FieldOrDefault(AllReplace,_,_)}})

        In Advanced Editor, I've added a #"AllReplace" which contains a list of old and new values (DIESEL is old value and DIESEL1 is new value to get replaced. Similarly, REG is an old value and REG1 will be a new value)

         

        Then add an another step, where I've used this list. You need to use "Table.TransformColumns" if you want to do all search and replace in single step but not "Table.ReplaceValue" 

    • iKiok's avatar
      iKiok
      Frequent Visitor
      7 years ago

      Greetings PattemManohar 

       

      is there a way to read #"DIESEL" and "DIESEL1" from columns of another table?

       

      so that there is a replacements table?

       

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    I would suggest you provide a dummy pbix file. We can give you a solution with details in the file. If you'd like to share a file, you can upload it to the cloud drive like OneDrive, GoogleDrive, then share the download link here. Please don't share sensitive data here.

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Dale,

       

      Sorry for the delay in response.

       

      I cannot attach the dataset but I have screenshots of what i'm talking about. 

      So I have 20 Replaced Value steps. 

       

      I had about the same number of steps for Renamed Columns and Changed Type, and I was able to combine these steps into one single step each using M-Query in the formula bar by using curly brackets and coma separators. 

       

      I want to do the same for the Replaced Value steps. However applying the same logic as I did for the Renamed Columns steps, does not work for Replaced Value. 

       

      So to summarise, I am wondering if it is possible to combine 'Replaced Value' Applied Steps using M-Query in the formula bar. 

       


      Example of where I have combined multiple steps of renaming columns by splitting using {},First Replace Value StepSecond Replace Value StepThird Replace Value StepApplied steps shows all the Replaced Value Steps

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale