cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ViDiBi
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
Drrickryp
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
Drrickryp
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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (55,095)