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..

     

     

Smauro's avatar
Smauro
Icon for Solution Sage rankSolution Sage
5 years ago

Hi Anonymous 

 

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