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

[Syndicate_Admin]

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

[Syndicate_Admin]

@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

[Syndicate_Admin]

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

[Syndicate_Admin]

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.