Forum Discussion

sbcict's avatar
sbcict
New Member
5 months ago
Solved

Replacing string values in a query column with values from another query

i have a column, titled "Expression", in a query table named "MOE" where each column row has a string of values separated by spaces, e.g. "A OR B OR C OR D AND NOT E", about 5000 rows long. i have a...
Need Help
  • mickey64's avatar
    mickey64
    5 months ago

    For your reference.

    I'm not sure if this method will shorten the processing time.

     

    Step 0: I use these data below.

    <MOE>

     

    <Product>

     

    Step 1: I duplicate 'Expression' column.

     

    Step 2: I split 'Expression - Copy' column by space.

    <After>

     

    Step 3: I unpivot 'Expression -Copy.1-' - 'Expression -Copy.10-' column.

     

    Step 4: I merge queries and expand product..

    <After>

     

    Step 5: I remove 'Expression' column and 'Value' column.

     

    Step 6: I pivot column.

    <After>

     

    Step 7: I reorder columns.

     

    Step 8: I merge columns with space.

    <After>

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 months ago

Hi,

Does this M code code work any better?

let
    Source = Excel.CurrentWorkbook(){[Name="Table28"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Amount", type number}, {"ID Category", type text}}),
    Custom1 = Table.ToRows(Table29),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.ReplaceMatchingItems(Text.Split([ID Category],","),Custom1),", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID Category"})
in
    #"Removed Columns"

 Table29 has 2 columns.  ID and Product Category.  Table28 has 2 columns Sales Amount and ID Category.  Under the ID Category column are comma seperated numbers in each cell.  So the code above iterated over every cell of the ID Category column and replaces every comma seperated number wti the Category availale in Table29.

Hope this helps.