Adding a new column from another with specific cell values

[ggfd]

Hello group,

I have a spreadsheet column with incorrect values. However, there's no "one size fits all". In other words, I cannot apply a reduction of 25% to the entire column, as some cells are correct, others require a price reduction of 4%, others 7%, etc.

E.g.

Product	Old Value	Fixed value
A	15	15
B	10	8
C	10	6.5

I wonder how the Fixed Value can be created by gathering info from Old Value. Perhaps a Python script can track the products names and correct the pricing?

@python 

[dufoq3]

Ho, yes, its M. You can see my notes below how to use it.

[ggfd]

Hello dufoq3,
Thanks so much for your fast response. I aprreciate that.
However, is that M? I'm asking because the code works, but I would not know how to replicate or modify it in the future.
That's why I've been asking to get this help in Python, or even in DAX if python is too hard.
Thanks for the help again,

[dufoq3]

Hi @ggfd,

you can achieve expected result in Power Query

If you add more Expected Revenue Columns then Fixed Expected Revenue columns will be added automatically.

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc9BC4JAEAXgvzII3URWY8lLh6B7QXUSDzaOZelszM4S/fuCzPb4YN7Hm6pK2EFH1J4bvEMnbgTiFjB4dSNJBtVxc9jmhTHrwi7qJE1Ohy18YmbMFPLUfGOdVklgHxDJ+y4MoOS150tkLIvZsLFR2FnYi7sRKmDDSMNAbbwhN2YGyhhY/oHd4+FEA/f6AhRqlFroGfRK8BT3mYNBhBhfMHVXNl7y/+XpOAXtRwJ1MDZyJ/0dlXlmy9//U6N+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Close Reason" = _t, #"Expected Revenue 2023" = _t, #"Expected Revenue 2024" = _t]),
    ChangedTypeRevenueDynamic = Table.TransformColumns(Source, List.Transform(List.Select(Table.ColumnNames(Source), each Text.Contains(_, "Revenue", Comparer.OrdinalIgnoreCase)),
      (colName)=> { colName, each Currency.From(Text.AfterDelimiter(_, " "), "en-US"), Currency.Type } )
  ),
    Ad_Percentage = Table.AddColumn(ChangedTypeRevenueDynamic, "Percentage", each Percentage.From(Text.BetweenDelimiters([Close Reason], "=", "]")), Percentage.Type),
    AddedFixedColumnsDynamic = List.Accumulate( 
    List.Select(Table.ColumnNames(Source), each Text.Contains(_, "Revenue", Comparer.OrdinalIgnoreCase)),
    Ad_Percentage,
    (s,c)=> Table.AddColumn(s, "Fixed " & c, each Record.Field(_, c) * (1 - [Percentage]), Currency.Type)
)
in
    AddedFixedColumnsDynamic

[ggfd]

2 seconds ago

Hello,
I'm sorry for the delay. I'm new on PowerBI so I was reviewing the problem to better describe what I'm trying to do.

Part of my dataset is below. The columns in red don't exist, so they are what I've been trying to create using python:

Close Reason (PowerBI column type: text)	Expected Revenue 2023 (PowerBI column Type: decimal number)	Fixed Expected Revenue 2023 (PowerBI column Type: decimal number)	Expected Revenue 2024 (PowerBI column Type: decimal number)	Fixed Expected Revenue 2024 (PowerBI column Type: decimal number)
no feedback from end customer. [TASD1200=25%]	USD 200.00		USD 1,000.00
unsuccessful testing [TASD1200=32%]	USD 500.00		USD 250.00
Project cancelled. [TASD1200=100%]	USD 800.00		USD 350.00
Opportunity created in the wrong currency	USD 750.00		USD 200.00
won, time to market	USD 281.58		USD 100.00

The new columns, "Fixed Expected Revenue 2023" and "Fixed Expected Revenue 2024," should be filled with the values obtained by subtracting the discount percentage specified in "Close Reason" column TASD1200 percentage from the "Expected Revenue 2023" and "Expected Revenue 2024" respectively.

Example:

Close Reason (PowerBI column type: text)	Expected Revenue 2023 (PowerBI column Type: decimal number)	Fixed Expected Revenue 2023 (PowerBI column Type: decimal number)	Expected Revenue 2024 (PowerBI column Type: decimal number)	Fixed Expected Revenue 2024 (PowerBI column Type: decimal number)
no feedback from end customer. [TASD1200=25%]	USD 200.00	200 - (200*25%) = 150	USD 1,000.00	1000 - (1000*25%) = 750

Is it possible to create that using python?

E.g.

Transform data > Run Python script?

I prefer to use Python rather than DAX. However, if Python is too complicated, a DAX script is fine.

Thanks a lot for the help,

[Anonymous · Accepted solution]

Hi @ggfd ,

You can achieve this in DAX easily if your data can be transformed into this:

The data types of all columns except the first are Decimal number.
You can use these DAXs to create two new columns:

Fixed Expected Revenue 2023 = 'Table'[Expected Revenue 2023 (PowerBI column Type: decimal number)] * (1 - 'Table'[TASD1200])

Fixed Expected Revenue 2024 = 'Table'[Expected Revenue 2024 (PowerBI column Type: decimal number)] * (1 - 'Table'[TASD1200])

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @ggfd ,

Please try this:
Here is my sample data:

Use this DAX to create a new column:

Fixed Value = 
SWITCH(
    TRUE(),
    'Table'[Product] = "B", 'Table'[Old Value] * 0.8,
    'Table'[Product] = "C" || 'Table'[Product] = "D", 'Table'[Old Value] * 0.65,
    'Table'[Old Value]
)

The final output is below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly.

Provide example input data.

Also provide the example desired output, with a clear step-by-step description of any calcutations the process flow.

Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Remember you are gerrting free expert help, so please put lots of proper effort to asking questions and providing example.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming