Efficient Query?

[DeltaMetal]

Hi

Is there a better way for writing the same code as I have do this on 8 columns and then repeat it for 5 Changes?

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value", each [Exchange1], each if[Exchange1] = "LIFFE" then [ExchangeName1] else [Exchange1], Replacer.ReplaceText,{"Exchange1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value", each [Exchange2], each if[Exchange2] = "LIFFE" then [ExchangeName2] else [Exchange2], Replacer.ReplaceText,{"Exchange2"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value", each [Exchange3], each if[Exchange3] = "LIFFE" then [ExchangeName3] else [Exchange3], Replacer.ReplaceText,{"Exchange3"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value", each [Exchange4], each if[Exchange4] = "LIFFE" then [ExchangeName4] else [Exchange4], Replacer.ReplaceText,{"Exchange4"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value", each [Exchange5], each if[Exchange5] = "LIFFE" then [ExchangeName5] else [Exchange5], Replacer.ReplaceText,{"Exchange5"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value", each [Exchange6], each if[Exchange6] = "LIFFE" then [ExchangeName6] else [Exchange6], Replacer.ReplaceText,{"Exchange6"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value", each [Exchange7], each if[Exchange7] = "LIFFE" then [ExchangeName7] else [Exchange7], Replacer.ReplaceText,{"Exchange7"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value", each [Exchange8], each if[Exchange8] = "LIFFE" then [ExchangeName8] else [Exchange8], Replacer.ReplaceText,{"Exchange8"}),

[v-yanjiang-msft]

Hi @DeltaMetal ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[wdx223_Daniel]

= let a=Table.ColumnNames(PreviousStepName) in Table.FromRecords(Table.TransformRows(PreviousStepName,each Record.TransformFields(_,List.Transform(a,(x)=>{x,(y)=>if Record.Field(_,x)="LIFFE" then Record.FieldOrDefault(_,Text.Replace(x,"Exchange","ExchangeName"),null) else y}))))

[AlexisOlson]

You can unpivot the ExchangeN columns before doing the replacement and pivot back if needed.

Try pasting this query into the Advanced Editor of a new blank query and look through each applied step for an example of what I mean:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vF0c3NV0lFyNAQSQA6YbQRhQzjGSrE60TApJ0MkKSdkdU5AdbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Exchange1 = _t, ExchangeName1 = _t, Exchange2 = _t, ExchangeName2 = _t, Exchange3 = _t, ExchangeName3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Exchange1", type text}, {"ExchangeName1", type text}, {"Exchange2", type text}, {"ExchangeName2", type text}, {"Exchange3", type text}, {"ExchangeName3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column", "ID"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Column]), "Column", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column", each [Exchange], each if [Exchange] = "LIFFE" then [ExchangeName] else [Exchange],Replacer.ReplaceText,{"Exchange"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Index", "ID"}, "Attribute", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Columns",each [Attribute], each [Attribute] & [ID],Replacer.ReplaceText,{"Attribute"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"ID"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column1",{"Index"})
in
    #"Removed Columns1"

The main idea is to reshape the data from something like this:

Into something like this:

And then do the replacement on a single column.

[Anonymous]

As far as I know you can only replace value on multiple columns if the value is the same...

= Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"Jobs Raised", "WeeklyUsage"})

would replace null with 0 on columns Jobs Raised and Weekly Usage.

I don't know of any way to combine replacements for different values i.e. if null = 0 if True =1 if False = 2 etc. across multiple columns so they would each need a seperate step.