Forum Discussion

BINewbie1's avatar
BINewbie1
Icon for Helper II rankHelper II
3 years ago
Solved

Data Cleaning - Pound, Pence and even Euros in the same Column

Hi there,

 

Started using Power BI last week so a real Newbie. I've a .csv file that i'm downloading from a website I use. Its financial infomation and there's one column I'm really struggling to organise. It represents a unit price, but it comes in various inconsistent formats. e.g.

 

111p

£344.98

€34.76

120.87p

 

Its a file I want to work with weekly, and there's about 50 values so I don't want to do it manually. So I need to come up with a Power BI query that will organise it consistently - probably using pence as the final unit. Ideally, converting the Euro to pense as well using a currency conversion from that week - not too much of an issue if I can't do that. Future data might include something is $, so it would be useful eventually.

 

Any thoughts? I've been faffing around try to use split by delimiter (p, £, or€) before and after to split out the figures from the text and then multipling the £ figures by 100, and then I have the different types of value in different columns, but I'm struggling to merge them all into one column and format it as a decimal number. I just end up with lots of errors and null values. I'm using a new conditional column.

 

Is there some sort of IF logic I could use? I couldn't seem to get anywhere with Column From Example either.

 

Many thanks,

 

Adam

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, BINewbie1 ;

    You could create a custom column by extract value ;

    =Text.Select([Unit Price],{"0".."9","."})

    Then create a conditional column.

    =if Text.Contains([Unit Price], "£") then [Currency]*100 
else if Text.Contains([Unit Price], "€") then [Currency]*87.19 
else if Text.Contains([Unit Price], "$") then [Currency]*10
else [Currency]

    The final show:

    and could delete other column.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    3 years ago

    BINewbie1 Try this:

     

    Conversion table:

    let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WKlDSUTJUitWJVjq0GMg0MjEAcx41rQHyLMz1DC2VYmMB",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Currency = _t, Conversion = _t ]
    ),
    ChangedType = 
        Table.TransformColumnTypes (
            Source,
            { { "Currency", type text }, { "Conversion", type number } }
        )
in
    ChangedType

     

    Main table:

    let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WMjQ0LFCK1YlWOrTY2MREz9ICzHnUtMbYRM/cDMwxNDLQszAHqooFAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ #"Unit Price" = _t ]
        ),
    ChangedType = Table.TransformColumnTypes ( Source, { { "Unit Price", type text } } ),
    GetCurrency = 
        Table.AddColumn (
            ChangedType,
            "Currency",
            each List.RemoveItems ( Text.ToList ( [Unit Price] ), { "0" .. "9", ".", ",", ":", ";" } ){
                0
            },
            type text
        ),
    RemoveCurrency = 
        Table.AddColumn (
            GetCurrency,
            "Unit Price.1",
            each Number.From ( Text.Replace ( [Unit Price], [Currency], "" ) ),
            type number
        ),
    RemovedColumns = Table.RemoveColumns ( RemoveCurrency, { "Unit Price" } ),
    MergedQueries = 
        Table.NestedJoin (
            RemovedColumns,
            { "Currency" },
            CurrencyConversion,
            { "Currency" },
            "CurrencyConversion",
            JoinKind.LeftOuter
        ),
    ExpandedCurrencyConversion = 
        Table.ExpandTableColumn (
            MergedQueries,
            "CurrencyConversion",
            { "Conversion" },
            { "Conversion" }
        ),
    InsertedMultiplication = 
        Table.AddColumn (
            ExpandedCurrencyConversion,
            "Multiplication",
            each [Unit Price.1] * [Conversion],
            type number
        )
in
    InsertedMultiplication
    • BINewbie1's avatar
      BINewbie1
      Icon for Helper II rankHelper II
      3 years ago

      Thanks AntrikshSharma,

       

      Appreciate the support.

       

      Struggling to work out how to paste that into the advance editor, which already has some text in it.  Obviously i need to edit the text, seems more than just adding #" to each line, and filling in the gaps?

       

      I did say I was very new to Power BI, I'm also very new to Power Query in general!

       

      Any step-by-step guide you can give to getting this into the Advanced Editor would be great.  or maybe is there a way to have this as a stand alone query as part of the data cleaning steps - anything to make it a bit simpler.

       

      Sorry if a stupid question.

       

      Thanks,

       

      Adam

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, BINewbie1 ;

    You could create a custom column by extract value ;

    =Text.Select([Unit Price],{"0".."9","."})

    Then create a conditional column.

    =if Text.Contains([Unit Price], "£") then [Currency]*100 
else if Text.Contains([Unit Price], "€") then [Currency]*87.19 
else if Text.Contains([Unit Price], "$") then [Currency]*10
else [Currency]

    The final show:

    and could delete other column.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.