none
Change Persian / Arabic numbers in the whole table RRS feed

  • Question

  • Hello everybody,

    I have a custom function that allows me to convert persian / arbian numbers into decimal numbers in one column. Sample file:
    https://www.dropbox.com/s/n5jl8xw5m1u23x1/TranslationTable%20Custom%20Function.xlsx?dl=1

    My goal is a function that has Source and TranslationTable as input and performs the conversion in all columns.

    I've seen codes like

    ColumnNames = Table.ColumnNames(MyTable),
    TableReplaced = Table.ReplaceValue(MyTable,  null, 0,  Replacer.ReplaceValue, ColumnNames)

    Is there a way to merge that togehter with my custom function?
    Or how can I call my custom function in all columns?

    Andreas.

    Sunday, June 16, 2019 10:48 AM

Answers

  • I'd recommend to use a function like this:

    = Table.TransformColumns(SourceTable, List.Transform(Table.ColumnNames(SourceTable), (x) => {x, each TranslationQuery(_)}))

    Also, you should buffer the translation table in the List.Generate-Function, as it will soon slow down if you apply it to multiple rows. Like so for example:

    (translationTable as table, TextColumn as text) as text =>
    
    let
      TranslationTable = Table.Buffer(translationTable),
      maxIterations = Table.RowCount(TranslationTable) ,
      Iterations = List.Generate( () =>
        [Result = Text.Replace(TextColumn, TranslationTable[OldText]{0}, TranslationTable[NewText]{0}), Counter = 0],
          each [Counter] < maxIterations,
          each [Result = Text.Replace([Result], TranslationTable[OldText]{Counter}, TranslationTable[NewText]{Counter}),
          Counter = [Counter] +1], 
        each [Result]),
      Output = Iterations{maxIterations-1}
    in
      Output

    Then you'd call it like so:

    = Table.TransformColumns(SourceTable, List.Transform(Table.ColumnNames(SourceTable), (x) => {x, each TranslationQuery2(TranslationTable, _)}))
    ( 2 function arguments "


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, June 16, 2019 9:11 PM
    Moderator

All replies

  • I'd recommend to use a function like this:

    = Table.TransformColumns(SourceTable, List.Transform(Table.ColumnNames(SourceTable), (x) => {x, each TranslationQuery(_)}))

    Also, you should buffer the translation table in the List.Generate-Function, as it will soon slow down if you apply it to multiple rows. Like so for example:

    (translationTable as table, TextColumn as text) as text =>
    
    let
      TranslationTable = Table.Buffer(translationTable),
      maxIterations = Table.RowCount(TranslationTable) ,
      Iterations = List.Generate( () =>
        [Result = Text.Replace(TextColumn, TranslationTable[OldText]{0}, TranslationTable[NewText]{0}), Counter = 0],
          each [Counter] < maxIterations,
          each [Result = Text.Replace([Result], TranslationTable[OldText]{Counter}, TranslationTable[NewText]{Counter}),
          Counter = [Counter] +1], 
        each [Result]),
      Output = Iterations{maxIterations-1}
    in
      Output

    Then you'd call it like so:

    = Table.TransformColumns(SourceTable, List.Transform(Table.ColumnNames(SourceTable), (x) => {x, each TranslationQuery2(TranslationTable, _)}))
    ( 2 function arguments "


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, June 16, 2019 9:11 PM
    Moderator
  • Hello Imke,

    that works great, thanks you so much.

    For followers: I've updated my sample file and added examples, same download link.

    Andreas.

    Monday, June 17, 2019 10:09 AM