Subtract two values with german format (Excel Source)

[ViDiBi]

Hi everybody!

Since this is the first app I've created this community helped a lot 🙂 So I hope you can tell me this strange behaviour:

I have a collection where I calculate the usage. This is done by looking up the previous month value and subtract both like this:

ClearCollect(
    Data_EnergySelected;
    AddColumns(
        Search(
            Data_Energy;
            Dropdown1.Selected.PK_Counter;
            "FK_Counter"
        );
        "Usage";
        Value(
            Text(
                Value;
                "[$-en]#.#"
            )
        ) - Value(
            Text(
                LookUp(
                    Search(
                        Data_Energy;
                        Dropdown1.Selected.PK_Counter;
                        "FK_Counter"
                    );
                    Date = Date;
                    Value
                );
                "[$-en]#.#"
            )
        )
    )
)

The original data source is an excel file where the values are saved in german format... If the value contains a ',' (which is the seperator in Germany) the subtract doesn't work correctly: For example 4 - 3,5 should be 0,5 as consumption but it calculates 4 - 35 = -31. I already tried to add Text(Value; "#,#") and Value(Value; "de-DE") and combined both and tried with en-EN) but it will not calculate 😞

What am I missing?

Thank you so much for your help!

Victor

[Drrickryp · Accepted solution]

Hi @ViDiBi 

I don't want to get into the weeds with your formula but you can use the Substitute() function to replace the comma in your Value 

1* Substitute(Value, ",", ".") 

and multiply it *1 to make sure that it is interpreted by PowerApps as a number rather than Text.  (this is a shortcut I use instead of wrapping a text field in Value() )

[ViDiBi]

Hi @Drrickryp !

Thank you very much for your help!!! That actualy did the trick! I hat do change it to this code:

1 * Substitute(Value;".";",")

which means in my code:

ClearCollect(
    Data_EnergySelected;
    AddColumns(
        Search(
            Data_Energy;
            Dropdown1.Selected.PK_Counter;
            "FK_Counter"
        );
        "Usage";
        1 * Substitute(
            Value;
            ".";
            ","
        ) - 1 * Substitute(LookUp(
            Search(
                Data_Energy;
                Dropdown1.Selected.PK_Counter;
                "FK_Counter"
            );
            Date = Date;
            Value
        );".";",")
    )
)

Have a great weekend 🙂

Victor