cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Subtract two values with german format (Excel Source)

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

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() )

 

View solution in original post

2 REPLIES 2
Super User II
Super User II

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() )

 

View solution in original post

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

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (47,840)