Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiply with difference columns based on a value

I have a query which need to multiply wiith different columns based on a column "Duration". Formula like this:   when duration is 1, I need to multiply commission amount, exchange rate and 1st year...
Need Help
  • Angith_Nair's avatar
    Angith_Nair
    5 years ago

    I have made a calculated column in the Power BI level. If you want to make this in Power Query then write the code like this..

    The result will be something like this..

     

     

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

Hi @KLee1024 

 

You could try adding this as a step, it skips unpivoting and just selects the correct column:

 

= let
    YearColumns = List.Select(
        Table.ColumnNames(#"Removed Columns"),
        each Text.EndsWith(_, "year"))
in
    Table.AddColumn(#"Removed Columns", "New Column",
    each [Commission Amount] * [#"Exchange Rate (History)"] *
    Record.FieldOrDefault(_, (List.Select(YearColumns,
    (t) => Number.From(Text.Select(t, {"0".."9"})) = [Duration] ){0}?)??"" ),
    type number)

 

Cheers