locked
How can I use wildcards for filtering/replacing in Power Query / Power BI? RRS feed

  • Question

  • As a data source I have a list of countries but some of them comes with a [x] attached at the end.  It is a footnote or something.

    So a list could be:

    Rank Country    Population
    1 China[b]              1,404,672,800
    2 India[c]              1,367,864,146
    3 United States[d] 330,397,710
    4 Indonesia                 269,603,400
    5 Pakistan[e]         220,892,331  

    With a wildcard I could replace the rows containing '[?]' with '' and the problem would be solved easily.  

    Is there any way to do it in Power Query??

    Thanks!

    Friday, October 2, 2020 12:21 AM

Answers

  • afaik there is no wilcard option available in PQ. check also  Replace Value/Text with pattern/wildcard

    in your case you could easily use Table.SplitColumn

    let
        source = #table({"rank","country","Population"},{{1,"China[a]",1404672800}}),
        split_by_delimiter = Table.SplitColumn(source, "country", Splitter.SplitTextByEachDelimiter({"["}, QuoteStyle.Csv, true), {"country"})
    in split_by_delimiter
    • Edited by anthony34 Friday, October 2, 2020 6:43 AM
    • Proposed as answer by Lz._ Friday, October 2, 2020 8:37 AM
    • Marked as answer by Imke FeldmannMVP Sunday, November 1, 2020 7:04 AM
    Friday, October 2, 2020 6:43 AM
  • A couple of other options

    1/ Add a custom column:

    Table.AddColumn(<PreviousStepName>, "CLEAN COUNTRY", each Text.BeforeDelimiter([Country],"["), type text)

    2/ Transform the [Country] column:

    Table.TransformColumns(<PreviousStepName>,
        {"Country", each Text.BeforeDelimiter(_,"["), type text}
    )


    • Edited by Lz._ Friday, October 2, 2020 7:29 AM
    • Proposed as answer by anthony34 Friday, October 2, 2020 8:38 AM
    • Marked as answer by Imke FeldmannMVP Sunday, November 1, 2020 7:04 AM
    Friday, October 2, 2020 7:25 AM

All replies

  • afaik there is no wilcard option available in PQ. check also  Replace Value/Text with pattern/wildcard

    in your case you could easily use Table.SplitColumn

    let
        source = #table({"rank","country","Population"},{{1,"China[a]",1404672800}}),
        split_by_delimiter = Table.SplitColumn(source, "country", Splitter.SplitTextByEachDelimiter({"["}, QuoteStyle.Csv, true), {"country"})
    in split_by_delimiter
    • Edited by anthony34 Friday, October 2, 2020 6:43 AM
    • Proposed as answer by Lz._ Friday, October 2, 2020 8:37 AM
    • Marked as answer by Imke FeldmannMVP Sunday, November 1, 2020 7:04 AM
    Friday, October 2, 2020 6:43 AM
  • A couple of other options

    1/ Add a custom column:

    Table.AddColumn(<PreviousStepName>, "CLEAN COUNTRY", each Text.BeforeDelimiter([Country],"["), type text)

    2/ Transform the [Country] column:

    Table.TransformColumns(<PreviousStepName>,
        {"Country", each Text.BeforeDelimiter(_,"["), type text}
    )


    • Edited by Lz._ Friday, October 2, 2020 7:29 AM
    • Proposed as answer by anthony34 Friday, October 2, 2020 8:38 AM
    • Marked as answer by Imke FeldmannMVP Sunday, November 1, 2020 7:04 AM
    Friday, October 2, 2020 7:25 AM