none
Count Delimiter RRS feed

  • Question

  • Hi: Total number of spaces is six hard coded below as a delimiter. Is there a way, can it be make dynamic?

    Text.AfterDelimiter([Column1], "      ", {0, RelativePosition.FromEnd})

    Thanks.

    Tuesday, June 11, 2019 6:31 AM

Answers

  • If the goal is simply to return the text at the end of a string regardless of the number of intervening spaces before the end text, wouldn't Text.AfterDelimiter([Column1], " ", {0, RelativePosition.FromEnd}) work? (just a single space as the second parameter).
    • Marked as answer by M.Awal Wednesday, June 12, 2019 11:32 AM
    Tuesday, June 11, 2019 10:02 PM
  • Hi,

    As an option, you may use such code:

    Text.Trim(Text.AfterDelimiter([Column1], "  ", {0, RelativePosition.FromEnd}))
    • Marked as answer by M.Awal Wednesday, June 12, 2019 11:32 AM
    Wednesday, June 12, 2019 11:24 AM

All replies

  • Hi,

    It's difficult to understand, what specific issue you mean. Provide sample data, please.

    Tuesday, June 11, 2019 10:32 AM
  • If the goal is simply to return the text at the end of a string regardless of the number of intervening spaces before the end text, wouldn't Text.AfterDelimiter([Column1], " ", {0, RelativePosition.FromEnd}) work? (just a single space as the second parameter).
    • Marked as answer by M.Awal Wednesday, June 12, 2019 11:32 AM
    Tuesday, June 11, 2019 10:02 PM
  • Hi: When I apply the spaces six times as delimiter, it extract the text as desired (shown in 2nd Column) such as Public Safety & PLA, QLD Assoc etc. However, if I use the same number of spaces in the second scenario, it doesn't extract the text properly for example, in the second column I don't want PSBA_INC.2016 but I want the rest. Same for second row and so forth. Thanks

    Text.AfterDelimiter([Column1], "      ", {0, RelativePosition.FromEnd})

    Scenario -1

    Column1 Text After Delimiter
    1500            Public Safety & PLA Public Safety & PLA
     |  |
     |--     1501            Queensland Assoc Queensland   Assoc
     |   |  |   |
     |     |--   ADMINISTER      Administered Administered
     |     |   |  |     |   |
     |     |   |--   ADMIN_TRAN      Administered Transactions Administered   Transactions
     |     |   |  |     |   |

    Scenario-2

    Column1 Text After Delimiter
    PSBA_INC.2016   Operating (Surplus) / Deficit PSBA_INC.2016   Operating (Surplus) / Deficit
     |  |
     |--     QPS_INCOME.REV  TOTAL REVENUE   FROM OPERATIONS  |--     QPS_INCOME.REV  TOTAL REVENUE   FROM OPERATIONS
     |   |  |   |
     |     |--   QPS_INCOME.AP   Output Revenue  |     |--   QPS_INCOME.AP   Output Revenue

    https://1drv.ms/x/s!Amc8fiGpDxekhTF-UUujWwrIWGrr

    Wednesday, June 12, 2019 11:02 AM
  • I think I found my own answer. If I reduce the number of spaces to Three in both scenarios. I can't use single space because there are spaces between words, the part of text that I want to extract.

    Colin is right with his recommendation

    • Marked as answer by M.Awal Wednesday, June 12, 2019 11:32 AM
    • Unmarked as answer by M.Awal Wednesday, June 12, 2019 11:32 AM
    Wednesday, June 12, 2019 11:22 AM
  • Hi,

    As an option, you may use such code:

    Text.Trim(Text.AfterDelimiter([Column1], "  ", {0, RelativePosition.FromEnd}))
    • Marked as answer by M.Awal Wednesday, June 12, 2019 11:32 AM
    Wednesday, June 12, 2019 11:24 AM
  • Thanks
    Wednesday, June 12, 2019 11:32 AM