cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User III
Super User III

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
Highlighted
Super User III
Super User III

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

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,040)