Importing Numbers With Different Culture and suffixes.pbix

parse numbers with different culture -- and substitutions - 2021-11-07.pbix

parse numbers with different culture -- and substitutions - 2021-11-07.pq

// cleaned final query
let
    Json = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjXUMVaK1YlWMtGxBNOGegYGBjompmAOSDYbJp0LZRgaGeuZmEJ5pnpArglQPUQZkOMLY+TCGN4wBlBNLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"raw source" = Table.DuplicateColumn( Json, "Text", "Raw Text"),
    Source = Table.TransformColumns( #"raw source",{{"Text", Text.Lower, type text}} ),
    #"replace all suffixes" = Table.TransformColumns( Source, {{"Text", replaceSuffixList, type text}}),

    argList  = {
        {"b", "000000000"},
        {"m", "000000"},
        {"k", "000"}
    },

    replaceSuffixList = (source as text) as any =>
        let 
            lower = Text.Lower(source),
            finalText = List.Accumulate(
                argList, lower,
                (currentText, args) =>
                    let
                        // "current" args is replacer args, 
                        // "state" currentText: is the result of multiple transforms
                        final = Text.Replace( currentText, args{0}, args{1} )
                    in 
                        final
            )
        in
            finalText,

    // original query
    #"duplicate column" = Table.DuplicateColumn(#"replace all suffixes", "Text", "As US"),
    #"convert using en" = Table.TransformColumnTypes(#"duplicate column",{{"As US", Currency.Type}}, "en-us"),
    #"duplicate column2" = Table.DuplicateColumn(#"convert using en", "Text", "As DE"),
    #"convert using de" = Table.TransformColumnTypes( #"duplicate column2",{{"As DE", Currency.Type}}, "de-de")
in
    #"convert using de"

screenshot.png