Forum Discussion

ggfd's avatar
ggfd
New Member
2 years ago
Solved

Adding a new column from another with specific cell values

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 cor...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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