Forum Discussion

arbarath's avatar
arbarath
Regular Visitor
4 years ago

Multiple Find and Replace from Lookup Table

Hi All, 

I am in a situation where I need to have a replace of Multiple Values in Column Separated by Comma, From the Look up Table. 

Example. 

FROMTO
Apple, Banana, OrangeAPL,BAN,ORG
Apple, MangoAPL,MNG
Orange, PineappleORG,PIN
Pineapple, BananaPIN,BAN

Lookup table

SOURCETARGET
AppleAPL
BananaBAN
OrangeORG
PineapplePIN
MangoMNG

I searched and got some help using this Clip https://www.youtube.com/watch?v=YWAMaas_1AU

Constructed Statement like this based on the Instructions

 

= Table.AddColumn(#"Previous Column", "TO", each List.Accumulate(
List.Numbers(0,Table.RowCount(TBL_LOOKUPTABLE)),
[FROM],
(state,current) =>
Text.Replace(state,TBL_LOOKUPTABLE[SOURCE]{current},TBL_LOOKUPTABLE[TARGET]{current})))

 

However, it seems to take too much time to process this and takes MB's of Loading compoared to original Load Size. 

I have roughly 3000 Rows of Source Data, where this needs to be done. 

Is there any better way. 

Yes, We can split column by delimiter, then lookup and again group by. Instead of this , is there any short method ?

 

Thank you. 

Need Help
Tips and Tricks

3 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi,

    Not sure it will be any more efficient, though you could try doing it with DAX. Assuming a table named 'Table' with a column 'FROM', this Calculated Column:

    TO =
VAR MyValues =
    ADDCOLUMNS( 'Table', "Paths", SUBSTITUTE( [FROM], ",", "|" ) )
VAR MyTable =
    SELECTCOLUMNS(
        GENERATE(
            MyValues,
            ADDCOLUMNS(
                GENERATESERIES( 1, PATHLENGTH( [Paths] ) ),
                "MyPaths", TRIM( PATHITEM( [Paths], [Value], TEXT ) )
            )
        ),
        "From", [FROM],
        "Paths", [MyPaths],
        "Target", LOOKUPVALUE( TBL_LOOKUPTABLE[TARGET], TBL_LOOKUPTABLE[SOURCE], [MyPaths] )
    )
RETURN
    CONCATENATEX( FILTER( MyTable, [From] = 'Table'[FROM] ), [Target], ", " )

    Regards

    • arbarath's avatar
      arbarath
      Regular Visitor
      4 years ago

      Jos_Woolley Thank you for the reply. However i am looking for a solution using PowerQuery M. 

      Sorry i am not good in DAX yet. And it takes time for me to understand what is being done. 

      However i tried to exactly copy paste your code. I have some problem If some values in source table are empty, it filles up with empty Comma  like

      ", ,,,,"

      Second, if some values are not found, it is repeated with one value found. 

      FROM to using the Calculated Column. 

       

       


      Lookup table

       

       

       

       

       

       

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    arbarath  There are some tricks you could try like Table.Buffer, or have a look at the Query Diagnostics and see if that tells you anything useful.

     

    You could write a custom function to split by delimiter into rows, merge queries for the lookup, then group again. Is your performance suffering with that option (will be the same with or without the custom function, but function will be easier to optimize).

     

    I do have a question why you need it all in one cell/column? Typically database should have one piece of info per cell, and then you aggregate it in the report. So maybe you can skip the Group By step in your alternate option and just do that in the report???